package service

import (
	"encoding/json"
	"errors"
	"fmt"
	ClickHouse "gin/database"
	"gin/model"
	"gin/types"
	"github.com/gin-gonic/gin"
	"gorm.io/datatypes"
	"gorm.io/gorm"
	"log"
	"sort"
	"strconv"
	"strings"
	"time"
)

type Task struct {
}

type UserManHourUser struct {
	Id     uint64 `json:"id"`
	Name   string `json:"name"`
	Phone  string `json:"phone"`
	Email  string `json:"email"`
	Avatar string `json:"avatar"`
}

type UserManHourList struct {
	ManHours uint64          `json:"man_hours" gorm:"column:man_hours"`
	UserId   uint64          `json:"user_id" gorm:"column:user_id"`
	User     UserManHourUser `json:"user"`
	Time     float64         `json:"time"`
	Percent  float64         `json:"percent"`
}

type StatisticsRes struct {
	Done       int64 `json:"done"`
	InProgress int64 `json:"in_progress"`
	OutDate    int64 `json:"out_date"`
	Total      int64 `json:"total"`
}

type StepCategoryStatistics struct {
	StepCategoryId uint64 `json:"step_category_id"`
	Count          uint64 `json:"count"`
}

type StepCategoryStatisticsRes struct {
	Title []string `json:"title"`
	Value []uint64 `json:"value"`
}

type Ids struct {
	Id uint64 `json:"id"`
}

type StepIdsGroup struct {
	StepCategoryId uint64 `json:"step_category_id"`
	Id             uint64 `json:"id"`
}

type StepTaskVersionCount struct {
	StepId uint64 `json:"step_id"`
	Count  uint64 `json:"count"`
}

type UserTaskVersionCount struct {
	UserId uint64 `json:"user_id"`
	Count  uint64 `json:"count"`
}

type UserDepartmentCount struct {
	UserId       uint64 `json:"user_id"`
	ProjectId    uint64 `json:"project_id"`
	DepartmentId uint64 `json:"department_id"`
	Count        uint64 `json:"count"`
}

type UserDepartmentManHourCount struct {
	UserId       uint64  `json:"user_id"`
	ProjectId    uint64  `json:"project_id"`
	DepartmentId uint64  `json:"department_id"`
	Count        float64 `json:"count"`
}

type TaskWorkload struct {
	Name  string `json:"name"`
	Count uint64 `json:"count"`
}

type TaskWorkloadUserCount struct {
	Total      uint64 `json:"total"`
	Investment uint64 `json:"investment"`
}

type UserTaskWorkloadRes struct {
	UserCount    TaskWorkloadUserCount `json:"user_count"`
	ProjectCount []TaskWorkload        `json:"project_count"`
}

type DepartmentCountItem struct {
	Id        uint64 `json:"id"`
	Name      string `json:"name"`       // 名称
	Type      string `json:"type"`       // 行类型
	TenantId  uint64 `json:"tenant_id"`  // 租户id
	ParentId  uint64 `json:"parent_id"`  // 父级id
	Count     uint64 `json:"count"`      // 版本提交数量
	DoneCount uint64 `json:"done_count"` // 任务完成数量
	PassCount uint64 `json:"pass_count"` // 版本通过数量
	UserId    uint64 `json:"user_id"`    // 用户id
	Role      string `json:"role"`       // 角色信息
}

type DepartmentRequiresCountItem struct {
	Id                 uint64 `json:"id"`
	Name               string `json:"name"`                  // 名称
	Type               string `json:"type"`                  // 行类型
	TenantId           uint64 `json:"tenant_id"`             // 租户id
	ParentId           uint64 `json:"parent_id"`             // 父级id
	NotStartTaskCount  uint64 `json:"not_start_task_count"`  // 未开始任务数量
	IpTaskCount        uint64 `json:"ip_task_count"`         // 进行中任务数量
	DoneTaskCount      uint64 `json:"done_task_count"`       // 已完成任务数量
	WaitForReviewCount uint64 `json:"wait_for_review_count"` // 待审核任务数量
	OverdueTaskCount   uint64 `json:"overdue_task_count"`    // 已经逾期任务总数
	UserId             uint64 `json:"user_id"`               // 用户id
	Role               string `json:"role"`                  // 角色信息
}

type DepartmentWorkloadCountItem struct {
	Id                  uint64  `json:"id"`
	Name                string  `json:"name"`                  // 名称
	Type                string  `json:"type"`                  // 行类型
	TenantId            uint64  `json:"tenant_id"`             // 租户id
	ParentId            uint64  `json:"parent_id"`             // 父级id
	UserTotalCount      uint64  `json:"user_total_count"`      // 用户总人数
	UserInvestmentCount uint64  `json:"user_investment_count"` // 已经用户总人数
	TaskManHour         float64 `json:"task_man_hour"`         // 进行中的任务总工时
	OverdueTaskCount    uint64  `json:"overdue_task_count"`    // 已经逾期任务总数
	UserId              uint64  `json:"user_id"`               // 用户id
	Role                string  `json:"role"`                  // 角色信息
}

type DepartmentCountList []DepartmentCountItem

type TaskCompletedDataStruct struct {
	ProjectId                 uint64
	EntityId                  uint64
	EntityName                string
	EntityCode                string
	EntityInitialEpisodeCode  string
	TaskId                    uint64
	TaskStepId                int64
	TaskStepCategoryId        uint64
	TaskProjectId             uint64
	TaskTenantId              uint64
	TaskDepartmentId          uint64
	TaskAssignee              uint64
	TaskExecutor              uint64
	TaskName                  string
	TaskPlanEndTime           uint64
	TaskEndTime               uint64
	TaskStatusId              int64
	TenantId                  uint64
	TenantName                string
	TenantStatus              string
	TenantCreated             uint64
	TenantRole                uint64
	TenantCreateBy            uint64
	DepartmentId              uint64
	DepartmentName            string
	DepartmentTenantId        uint64
	DepartmentParentId        uint64
	DepartmentCreated         uint64
	DepartmentStepCategoryIds string
}

type WorkOrderSituationStruct struct {
	TaskWorkOrderId                   uint64
	TaskWorkOrderNumber               string
	TaskWorkOrderName                 string
	TaskWorkOrderReportType           string
	TaskWorkOrderReportContent        string
	TaskWorkOrderRelateStepCategoryId uint64
	TaskWorkOrderCreated              uint64
	TaskWorkOrderProcessedPeople      string
	TaskWorkOrderTreatment            string
	TaskWorkOrderIncidence            string
	TaskWorkOrderStatus               uint64
	TaskWorkOrderProjectId            uint64
	TaskWorkOrderCreatedBy            uint64
	TaskWorkOrderReceiver             uint64
	TaskWorkOrderCloseAt              uint64
	EntityId                          uint64
	EntityName                        string
	EntityCode                        string
	EntityInitialEpisodeCode          string
	EntityShowingsNumber              string
	EntityModuleId                    uint64
	TaskId                            uint64
	TaskStepId                        uint64
	TaskStepCategoryId                uint64
}

type resWorkOrderSituationStruct struct {
	TaskId              int64
	ProjectId           int64
	WorkOrderStatus     int64
	WorkOrderReportType string
}

type resTaskCompletionStruct struct {
	TaskId        int64
	ProjectId     int64
	TaskIsOverdue string
	TaskStatus    int64
	TaskStepType  string
}

type resShouldBeDeliveredDataStruct struct {
	Id int64
}

// 实现部门统计数据的排序
func (DL DepartmentCountList) Len() int { return len(DL) }
func (DL DepartmentCountList) Less(i, j int) bool {
	return (DL[i].Count > DL[j].Count) || (DL[i].Count == DL[j].Count && DL[i].DoneCount > DL[j].DoneCount) || (DL[i].Count == DL[j].Count && DL[i].DoneCount == DL[j].DoneCount && DL[i].PassCount > DL[j].PassCount)
}
func (DL DepartmentCountList) Swap(i, j int) { DL[i], DL[j] = DL[j], DL[i] }

type DepartmentRequiresCountList []DepartmentRequiresCountItem

// 实现部门统计数据的排序
func (DL DepartmentRequiresCountList) Len() int { return len(DL) }
func (DL DepartmentRequiresCountList) Less(i, j int) bool {
	return (DL[i].IpTaskCount > DL[j].IpTaskCount) || (DL[i].IpTaskCount == DL[j].IpTaskCount && DL[i].WaitForReviewCount > DL[j].WaitForReviewCount)
}
func (DL DepartmentRequiresCountList) Swap(i, j int) { DL[i], DL[j] = DL[j], DL[i] }

type DepartmentWorkloadCountList []DepartmentWorkloadCountItem

// 实现部门统计数据的排序
func (DL DepartmentWorkloadCountList) Len() int { return len(DL) }
func (DL DepartmentWorkloadCountList) Less(i, j int) bool {
	return (DL[i].UserInvestmentCount > DL[j].UserInvestmentCount) || (DL[i].UserInvestmentCount == DL[j].UserInvestmentCount && DL[i].TaskManHour > DL[j].TaskManHour)
}
func (DL DepartmentWorkloadCountList) Swap(i, j int) { DL[i], DL[j] = DL[j], DL[i] }

// GetFirstDayOfMonth 获得一个月的第一天
func GetFirstDayOfMonth(d time.Time) time.Time {
	d = d.AddDate(0, 0, -d.Day()+1)
	return GetZeroTime(d)
}

// GetLastDayOfMonth 获得一个月的最后一天
func GetLastDayOfMonth(d time.Time) time.Time {
	return GetFirstDayOfMonth(d).AddDate(0, 1, -1)
}

// GetZeroTime 获得一天的0点
func GetZeroTime(d time.Time) time.Time {
	return time.Date(d.Year(), d.Month(), d.Day(), 0, 0, 0, 0, d.Location())
}

// WorkRank 工时排行
func (t *Task) WorkRank(tenantId int64, limit int) (UserManHourRes []UserManHourList, err error) {
	monthStartTime := GetFirstDayOfMonth(time.Now().AddDate(0, -5, 0))

	dbr := ClickHouse.DB.Model(&model.TimeLog{}).
		Where("tenant_id = ?", tenantId).
		Where("complete = 'yes'").
		Where("end_time - start_time > ?", 0.01*(60*60)).
		Where("start_time >= ?", monthStartTime.Unix()).
		Order("man_hours DESC").
		Limit(limit).
		Group("created_by").
		Select("SUM(end_time - start_time) AS man_hours,created_by AS user_id").
		Find(&UserManHourRes)

	if dbr.Error != nil {
		err = dbr.Error
		return
	}

	if len(UserManHourRes) == 0 {
		return
	}
	var maxHour = float64(UserManHourRes[0].ManHours / (60 * 60))
	var userIdList = make([]uint64, len(UserManHourRes))
	for i, userManHour := range UserManHourRes {
		userIdList[i] = userManHour.UserId
	}
	var userList = make([]UserManHourUser, len(UserManHourRes))

	err = ClickHouse.DB.Model(&model.User{}).
		Where("id in ?", userIdList).Select("id,name,phone,email,avatar").Find(&userList).Error
	if err != nil {
		return
	}
	for i := range UserManHourRes {
		userid := UserManHourRes[i].UserId
		UserManHourRes[i].Time = float64(UserManHourRes[i].ManHours / (60 * 60))
		UserManHourRes[i].Percent, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", UserManHourRes[i].Time/maxHour), 2)
		for _, user := range userList {
			if user.Id == userid {
				UserManHourRes[i].User = user
				break
			}
		}
	}

	return UserManHourRes, err
}

// Statistics 任务数量统计
func (t *Task) Statistics(statusCorrespondData map[string][]string, tenantId int64) (res StatisticsRes, err error) {

	// 所有
	err = ClickHouse.DB.Model(&model.Task{}).Where("tenant_id = ?", tenantId).Count(&res.Total).Error
	if err != nil {
		return
	}

	// 进行中
	if _, ok := statusCorrespondData["in_progress"]; ok {
		err = ClickHouse.DB.Model(&model.Task{}).Where("tenant_id = ? and simpleJSONExtractString(task.json, 'task_status') in ?", tenantId, statusCorrespondData["in_progress"]).Count(&res.InProgress).Error
		if err != nil {
			return
		}
	}

	// 逾期
	err = ClickHouse.DB.Model(&model.Task{}).Where("tenant_id = ? and plan_end_time > 0 and end_time > plan_end_time", tenantId).Count(&res.OutDate).Error
	if err != nil {
		return
	}

	// 完成
	var doneStatusList = make([]string, 0)
	if _, ok := statusCorrespondData["done"]; ok {
		for _, i := range statusCorrespondData["done"] {
			doneStatusList = append(doneStatusList, i)
		}
	}
	if _, ok := statusCorrespondData["hide"]; ok {
		for _, i := range statusCorrespondData["hide"] {
			doneStatusList = append(doneStatusList, i)
		}
	}

	if len(doneStatusList) > 0 {
		err = ClickHouse.DB.Model(&model.Task{}).Where("tenant_id = ? and simpleJSONExtractString(task.json, 'task_status') in ?", tenantId, doneStatusList).Count(&res.Done).Error
		if err != nil {
			return
		}
	}

	return
}

// getVersionSubmitMap 版本提交数量 按日map
func (t *Task) getVersionSubmitMap(days uint64) (res map[string]uint64, err error) {

	//var base int64
	//err = ClickHouse.DB.Table("version").
	//	Where("created < toUnixTimestamp(DATE_SUB(now(), INTERVAL (?) day))", days).
	//	Count(&base).Error
	//if err != nil {
	//	return
	//}
	var versionSubmitCount []types.DateCountRes
	err = ClickHouse.DB.Table("version v").
		Where("created >= toUnixTimestamp(DATE_SUB(date_trunc('day',now()), INTERVAL (?) day)) and created < toUnixTimestamp(date_trunc('day',now()))", days).
		Select("toString(toDate(created)) as date, count(*) as count").
		Group("toDate(created)").
		Find(&versionSubmitCount).Error
	if err != nil {
		return
	}
	res = make(map[string]uint64, len(versionSubmitCount))
	for _, countRes := range versionSubmitCount {
		res[countRes.Date] = countRes.Count
	}
	return
}

// getVersionPassMap 版本提交数量 按日map
func (t *Task) getVersionPassMap(days uint64) (res map[string]uint64, err error) {
	// 版本通过
	//err = ClickHouse.DB.Table("(?) as t", ClickHouse.DB.Table("version v").
	//	Joins("LEFT JOIN review_request rr on rr.link_id = v.id and rr.module_id = 54").
	//	Where("v.is_pass = 'yes'").
	//	Select("ifNull(rr.reviewed, v.created) as pass_time")).
	//	Where("pass_time < toUnixTimestamp(DATE_SUB(now(), INTERVAL (?) day))", days).
	//	Pluck("count(*)", &base).Error
	//if err != nil {
	//	return
	//}
	var passCount []types.DateCountRes
	err = ClickHouse.DB.Table("(?) as t", ClickHouse.DB.Table("version v").
		Joins("LEFT JOIN (select * from review_request where module_id = 54) rr on rr.link_id = v.id").
		Where("v.is_pass = 'yes'").
		Select("ifNull(rr.reviewed, v.created) as pass_time")).
		Where("pass_time >= toUnixTimestamp(DATE_SUB(date_trunc('day',now()), INTERVAL (?) day)) and pass_time < toUnixTimestamp(date_trunc('day',now()))", days).
		Group("toDate(pass_time)").
		Select("count(*) as count,toString(toDate(pass_time)) as date").Find(&passCount).Error
	if err != nil {
		return
	}
	res = make(map[string]uint64, len(passCount))
	for _, countRes := range passCount {
		res[countRes.Date] = countRes.Count
	}
	return
}

// VersionSubmitTrend 任务提交趋势图
func (t *Task) VersionSubmitTrend(days uint64) (res types.VersionTrendRes, err error) {
	versionSubmitCountMap, err := t.getVersionSubmitMap(days)
	if err != nil {
		return
	}
	passCountMap, err := t.getVersionPassMap(days)
	if err != nil {
		return
	}
	var versionSubmitTrendRes = types.VersionTrendItem{
		Title: "版本提交数",
	}
	var passTrendRes = types.VersionTrendItem{
		Title: "版本通过数",
	}
	res.Date = GetBetweenDates(time.Now().AddDate(0, 0, -int(days)).Format("2006-01-02"), time.Now().AddDate(0, 0, -1).Format("2006-01-02"))

	versionSubmitNum := uint64(0)
	passNum := uint64(0)
	for _, s := range res.Date {
		versionSubmitNum = 0
		passNum = 0
		if _, ok := versionSubmitCountMap[s]; ok {
			versionSubmitNum = versionSubmitCountMap[s]
		}
		if _, ok := passCountMap[s]; ok {
			passNum = passCountMap[s]
		}
		versionSubmitTrendRes.Value = append(versionSubmitTrendRes.Value, versionSubmitNum)
		passTrendRes.Value = append(passTrendRes.Value, passNum)
	}
	res.List = []types.VersionTrendItem{passTrendRes, versionSubmitTrendRes}
	return
}

// StepCategoryStatistics 分类统计数据
func (t *Task) StepCategoryStatistics(tenantId int64) (res StepCategoryStatisticsRes, err error) {
	// 查询分类统计数据
	var stepCategoryCount []StepCategoryStatistics
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN step_category on task.step_category_id = step_category.id").
		Where("task.tenant_id = ?", tenantId).
		Group("step_category.parent_id").
		Select("step_category.parent_id as step_category_id,count(id) AS count").
		Find(&stepCategoryCount)
	if dbr.Error != nil {
		err = dbr.Error
		return
	}
	if len(stepCategoryCount) == 0 {
		return
	}
	// 查询分类信息
	var stepCategoryIdList = make([]uint64, 0, len(stepCategoryCount))
	for _, statistics := range stepCategoryCount {
		stepCategoryIdList = append(stepCategoryIdList, statistics.StepCategoryId)
	}
	var stepCategoryList []model.StepCategory
	dbr = ClickHouse.DB.Model(&model.StepCategory{}).Where("id in ?", stepCategoryIdList).Order("index ASC").Find(&stepCategoryList)
	if dbr.Error != nil {
		err = dbr.Error
		return
	}

	// 构造分类id count map
	var stepCategoryIdCountMap = make(map[uint64]uint64, len(stepCategoryCount))
	for _, statistics := range stepCategoryCount {
		stepCategoryIdCountMap[statistics.StepCategoryId] = statistics.Count
	}
	// 拼接res
	for _, category := range stepCategoryList {
		res.Title = append(res.Title, category.Name)
		res.Value = append(res.Value, stepCategoryIdCountMap[category.Id])
	}

	return
}

// 统计每个工序在当前时间段任务版本提交数量
func countVersionByStepIds(tenantId int64, stepIds []uint64, startTime uint64, endTime uint64, projectId uint64) (stepTaskVersionCountMap map[uint64]uint64, err error) {
	var stepTaskVersionCount []StepTaskVersionCount
	dbr := ClickHouse.DB.Model(&model.Version{})

	dbr.Joins("LEFT JOIN task on task.id = version.link_id").
		Where("task.tenant_id = ?", tenantId).
		Where("task.step_id in (?) ", stepIds).
		Where("version.created  BETWEEN ? AND ?", startTime, endTime)

	if projectId > 0 {
		dbr.Where("task.project_id = ?", projectId)
	}

	dbr.Group("task.step_id").
		Select("task.step_id,count(id) AS count").
		Find(&stepTaskVersionCount)

	if dbr.Error != nil {
		err = dbr.Error
	}

	// 生成 step_id 数据字典
	var stepIdMap = make(map[uint64]uint64)
	for _, stepItem := range stepTaskVersionCount {
		stepIdMap[stepItem.StepId] = stepItem.Count
	}

	stepTaskVersionCountMap = stepIdMap

	return
}

// 按用户分组获取指定当前时间范围提交版本数量映射字典
func countVersionByUserIds(tenantId int64, distinctUserIds []uint64, startTime uint64, endTime uint64, projectId uint64) (userTaskVersionCountMap map[uint64]uint64, err error) {
	var userTaskVersionCount []UserTaskVersionCount
	dbr := ClickHouse.DB.Model(&model.Version{})

	dbr.Joins("LEFT JOIN task on task.id = version.link_id").
		Where("task.tenant_id = ?", tenantId).
		Where("version.created_by in (?) ", distinctUserIds).
		Where("version.created  BETWEEN ? AND ?", startTime, endTime)

	if projectId > 0 {
		dbr.Where("task.project_id = ?", projectId)
	}

	dbr.Group("version.created_by").
		Select("version.created_by as user_id,count(id) AS count").
		Find(&userTaskVersionCount)

	if dbr.Error != nil {
		err = dbr.Error
	}

	// 生成 user_id 数据字典
	var userIdMap = make(map[uint64]uint64)
	for _, stepItem := range userTaskVersionCount {
		userIdMap[stepItem.UserId] = stepItem.Count
	}

	userTaskVersionCountMap = userIdMap

	return
}

// 按用户分组获取指定当前时间范围提交版本数量映射字典
func countVersionByUserIdsGroupByDepartmentIdUserId(tenantId int64, distinctUserIds []uint64, startTime uint64, endTime uint64, projectId uint64) (userTaskVersionCountMap map[string]uint64, err error) {
	if len(distinctUserIds) == 0 {
		return
	}
	var userTaskVersionCount []UserDepartmentCount
	dbr := ClickHouse.DB.Model(&model.Version{})

	dbr.Joins("LEFT JOIN task on task.id = version.link_id").
		Where("task.tenant_id = ?", tenantId).
		Where("version.created_by in (?) ", distinctUserIds).
		Where("version.created BETWEEN ? AND ?", startTime, endTime)

	if projectId > 0 {
		dbr.Where("task.project_id = ?", projectId)
	}

	err = dbr.Group("version.created_by,task.department_id").
		Select("version.created_by as user_id,task.department_id,count(version.id) AS count").
		Find(&userTaskVersionCount).Error

	if err != nil {
		return
	}

	// 生成 user_id 数据字典
	var userIdMap = make(map[string]uint64, 0)
	for _, stepItem := range userTaskVersionCount {
		userIdMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = stepItem.Count
	}

	userTaskVersionCountMap = userIdMap

	return
}

// CountDepartmentVersionByStepCategory 按部门统计工序分类的某个时间段的版本提交次数
func (t *Task) CountDepartmentVersionByStepCategory(tenantId int64, startTime uint64, endTime uint64, projectId uint64) (departmentList DepartmentCountList, err error) {

	////查询当前所属当前租户的所有部门
	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getDepartmentList(tenantId, nil)

	if err != nil {
		return
	}

	// 获取指定租户下面所有用户及其角色标签
	var UserService User

	// 部门 ids
	var departmentIds []uint64
	for _, departmentItem := range departmentListQueryRes {
		departmentList = append(departmentList, DepartmentCountItem{
			Id:       departmentItem.Id,
			Name:     departmentItem.Name,
			Type:     "department",
			TenantId: departmentItem.TenantId,
			ParentId: departmentItem.ParentId,
			Count:    0,
			UserId:   0,
			Role:     "",
		})
		departmentIds = append(departmentIds, departmentItem.Id)
	}

	departmentUsers, distinctUserIds, departmentUserErr := UserService.GetDepartmentUserMultiDepartmentUser(departmentIds, tenantId)

	if departmentUserErr != nil {
		err = departmentUserErr
		return
	}

	// 按用户分组获取指定当前时间范围提交版本数量映射字典
	userVersionCount, err := countVersionByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, startTime, endTime, projectId)

	if err != nil {
		return
	}

	// 指定当前时间范围版本通过数量字典
	userVersionPassCount, err := countVersionPassedByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, startTime, endTime, projectId)
	if err != nil {
		return
	}

	// 按用户分组获取指定当前时间范围提交版本数量映射字典
	userTaskDoneCount, err := countTaskDoneByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, startTime, endTime, projectId)
	if err != nil {
		return
	}
	// 把用户提交版本数据回插入到部门
	var tmpCount uint64
	var tmpDoneCount uint64
	var tmpPassCount uint64
	var countKey string
	for _, departmentUserItem := range departmentUsers {
		tmpDoneCount = 0
		tmpCount = 0
		tmpPassCount = 0
		countKey = fmt.Sprintf("%d_%d", departmentUserItem.UserId, departmentUserItem.DepartmentId)
		if _, ok := userVersionCount[countKey]; ok {
			tmpCount = userVersionCount[countKey]
		}
		if _, ok := userTaskDoneCount[countKey]; ok {
			tmpDoneCount = userTaskDoneCount[countKey]
		}
		if _, ok := userVersionPassCount[countKey]; ok {
			tmpPassCount = userVersionPassCount[countKey]
		}
		departmentList = append(departmentList, DepartmentCountItem{
			Id:        0,
			Name:      departmentUserItem.UserName,
			Type:      "user",
			TenantId:  departmentUserItem.TenantId,
			ParentId:  departmentUserItem.DepartmentId,
			Count:     tmpCount,
			DoneCount: tmpDoneCount,
			PassCount: tmpPassCount,
			UserId:    departmentUserItem.UserId,
			Role:      departmentUserItem.RoleName,
		})
	}
	if departmentList.Len() > 0 {
		sort.Sort(departmentList)
	}

	return
}

// countVersionPassedByUserIdsGroupByDepartmentIdUserId 统计版本通过数量 (部门-人员=>数量)
// 审核时间在范围内的已通过版本数量
func countVersionPassedByUserIdsGroupByDepartmentIdUserId(tenantId int64, distinctUserIds []uint64, startTime uint64, endTime uint64, projectId uint64) (userTaskVersionCountMap map[string]uint64, err error) {
	if len(distinctUserIds) == 0 {
		return
	}
	var userTaskVersionCount []UserDepartmentCount

	var moduleModel = model.Module{}
	versionModule, err := moduleModel.GetByCode("version")
	if err != nil {
		return
	}

	dbr := ClickHouse.DB.Model(&model.Version{})

	dbr.Joins("LEFT JOIN task on task.id = version.link_id").
		Joins("JOIN review_request rr on version.id = rr.link_id").
		Where("rr.module_id = ?", versionModule.Id).
		Where("task.tenant_id = ?", tenantId).
		Where("version.created_by in (?) ", distinctUserIds).
		Where("version.is_pass = ?", "yes").
		Where("rr.reviewed BETWEEN ? AND ?", startTime, endTime)

	if projectId > 0 {
		dbr.Where("task.project_id = ?", projectId)
	}

	err = dbr.Group("version.created_by,task.department_id").
		Select("version.created_by as user_id,task.department_id as department_id,count(version.id) AS count").
		Find(&userTaskVersionCount).Error

	if err != nil {
		return
	}

	// 生成 user_id 数据字典
	var userIdMap = make(map[string]uint64, 0)
	for _, stepItem := range userTaskVersionCount {
		userIdMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = stepItem.Count
	}

	userTaskVersionCountMap = userIdMap

	return
}

// countTaskDoneByUserIdsGroupByDepartmentIdUserId 统计任务完成数量
func countTaskDoneByUserIdsGroupByDepartmentIdUserId(tenantId int64, distinctUserIds []uint64, startTime uint64, endTime uint64, projectId uint64) (userTaskVersionCountMap map[string]uint64, err error) {
	if len(distinctUserIds) == 0 {
		return
	}
	var userDepartmentCounts []UserDepartmentCount

	doneStatus, err := model.GetStatusByCode("final")
	if err != nil {
		return
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ?", tenantId).
		Where("task.executor in (?) ", distinctUserIds).
		Where("simpleJSONExtractUInt(task.json,'task_status') = ?", doneStatus.Id).
		Where("task.end_time BETWEEN ? AND ?", startTime, endTime)

	if projectId > 0 {
		dbr.Where("task.project_id = ?", projectId)
	}

	err = dbr.Group("task.executor,task.department_id").
		Select("task.executor as user_id,task.department_id,count(task.id) AS count").
		Find(&userDepartmentCounts).Error

	if err != nil {
		return
	}

	// 生成 user_id 数据字典
	var userIdMap = make(map[string]uint64, 0)
	for _, stepItem := range userDepartmentCounts {
		userIdMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = stepItem.Count
	}

	userTaskVersionCountMap = userIdMap

	return
}

// 按用户分组获取指定当前时间范围待审核任务数量映射字典
func countReviewTaskByUserIdsGroupByDepartmentIdUserId(tenantId int64, distinctUserIds []uint64, startTime uint64, endTime uint64, projectIds []uint64) (userTaskVersionCountMap map[string]uint64, err error) {
	var userDepartmentCounts []UserDepartmentCount

	// 获取进行中任务状态列表
	var StatusService Status
	corresponds := []string{"done", "feedback", "daily", "in_progress"}
	ipStatusIds, _ := StatusService.GetStatusIdsByCorrespond(corresponds)

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN task_clock tc on tc.task_id = task.id").
		Where("task.tenant_id = ?", tenantId).
		Where("task.executor in (?) ", distinctUserIds).
		Where("simpleJSONExtractUInt(task.json,'task_status') in ?", ipStatusIds).
		Where("task_clock.time BETWEEN ? AND ?", startTime, endTime)

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Group("task.executor,task.department_id").
		Select("task.executor as user_id,task.department_id,count(task.id) AS count").
		Find(&userDepartmentCounts).Error

	if err != nil {
		return
	}

	// 生成 user_id 数据字典
	var userIdMap = make(map[string]uint64, 0)
	for _, stepItem := range userDepartmentCounts {
		userIdMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = stepItem.Count
	}

	userTaskVersionCountMap = userIdMap

	return
}

// 按用户分组获取指定当前时间范围指定状态的任务数量映射字典
func countTaskWithStatusByUserIdsGroupByDepartmentIdUserId(tenantId int64, distinctUserIds []uint64, startTime uint64, endTime uint64, projectIds []uint64, corresponds []string) (userTaskVersionCountMap map[string]uint64, err error) {

	if len(distinctUserIds) == 0 {
		return
	}

	var userDepartmentCounts []UserDepartmentCount

	// 获取进行中任务状态列表
	var StatusService Status
	ipStatusIds, _ := StatusService.GetStatusIdsByCorrespond(corresponds)
	if len(ipStatusIds) == 0 {
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ?", tenantId).
		Where("task.executor in (?) ", distinctUserIds).
		Where("simpleJSONExtractUInt(task.json,'task_status') in ?", ipStatusIds).
		Where("task.plan_end_time BETWEEN ? AND ?", startTime, endTime)

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Group("task.executor,task.department_id").
		Select("task.executor as user_id,task.department_id,count(task.id) AS count").
		Find(&userDepartmentCounts).Error

	if err != nil {
		return
	}

	// 生成 user_id 数据字典
	var userIdMap = make(map[string]uint64, 0)
	for _, stepItem := range userDepartmentCounts {
		userIdMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = stepItem.Count
	}

	userTaskVersionCountMap = userIdMap

	return
}

// RequiresStatistics 获取指定时间段任务待审核数量和待交付总数
// 通过 task_clock表 获取带审核的任务（任务属于非完成状态）
// 通过 task表 end_time 获取带提交任务（任务属于非完成状态）
func (t *Task) RequiresStatistics(tenantId int64, projectIds []uint64, planEndTimeFrom uint64, planEndTimeTo uint64) (departmentList DepartmentRequiresCountList, err error) {
	// 查询当前所属当前租户的所有部门
	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getDepartmentList(tenantId, nil)

	if err != nil {
		return
	}

	// 部门 ids
	var departmentIds []uint64
	for _, departmentItem := range departmentListQueryRes {
		departmentList = append(departmentList, DepartmentRequiresCountItem{
			Id:                 departmentItem.Id,
			Name:               departmentItem.Name,
			Type:               "department",
			TenantId:           departmentItem.TenantId,
			ParentId:           departmentItem.ParentId,
			NotStartTaskCount:  0,
			IpTaskCount:        0,
			WaitForReviewCount: 0,
			DoneTaskCount:      0,
			OverdueTaskCount:   0,
			UserId:             0,
			Role:               "",
		})
		departmentIds = append(departmentIds, departmentItem.Id)
	}

	// 获取指定租户下面所有用户及其角色标签
	var UserService User

	departmentUsers, distinctUserIds, departmentUserErr := UserService.GetDepartmentUserMultiDepartmentUser(departmentIds, tenantId)

	if departmentUserErr != nil {
		err = departmentUserErr
		return
	}

	// 按用户分组获取指定当前时间范围待审核任务数量映射字典
	userReviewTaskCount, err := countTaskWithStatusByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, planEndTimeFrom, planEndTimeTo, projectIds, []string{"daily"})

	if err != nil {
		return
	}

	// 按用户分组获取指定当前时间范围未开始任务数量映射字典
	userNotStartTaskCount, err := countTaskWithStatusByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, planEndTimeFrom, planEndTimeTo, projectIds, []string{"not_started"})

	if err != nil {
		return
	}

	// 按用户分组获取指定当前时间范围进行中任务数量映射字典
	userIPTaskCount, err := countTaskWithStatusByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, planEndTimeFrom, planEndTimeTo, projectIds, []string{"feedback", "in_progress"})

	if err != nil {
		return
	}

	// 按用户分组获取指定当前时间范围已完成任务数量映射字典
	userDoneTaskCount, err := countTaskWithStatusByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, planEndTimeFrom, planEndTimeTo, projectIds, []string{"done"})

	if err != nil {
		return
	}

	// 按用户分组获取指定租户任务，不同项目逾期任务映射字典
	userOverdueTaskCountMap, err := countOverdueTaskByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, projectIds)

	if err != nil {
		return
	}

	// 把用户提交版本数据回插入到部门
	var tmpWaitForReviewCount uint64
	var tmpNotStartTaskCount uint64
	var tmpIPTaskCount uint64
	var tmpDoneTaskCount uint64
	var tmpOverdueTaskCount uint64

	var countKey string
	for _, departmentUserItem := range departmentUsers {
		tmpWaitForReviewCount = 0
		tmpIPTaskCount = 0
		tmpNotStartTaskCount = 0
		tmpDoneTaskCount = 0
		tmpOverdueTaskCount = 0
		countKey = fmt.Sprintf("%d_%d", departmentUserItem.UserId, departmentUserItem.DepartmentId)
		if _, ok := userReviewTaskCount[countKey]; ok {
			tmpWaitForReviewCount = userReviewTaskCount[countKey]
		}
		if _, ok := userIPTaskCount[countKey]; ok {
			tmpIPTaskCount = userIPTaskCount[countKey]
		}
		if _, ok := userNotStartTaskCount[countKey]; ok {
			tmpNotStartTaskCount = userNotStartTaskCount[countKey]
		}
		if _, ok := userDoneTaskCount[countKey]; ok {
			tmpDoneTaskCount = userDoneTaskCount[countKey]
		}
		if _, ok := userOverdueTaskCountMap[countKey]; ok {
			tmpOverdueTaskCount = userOverdueTaskCountMap[countKey]
		}
		departmentList = append(departmentList, DepartmentRequiresCountItem{
			Id:                 0,
			Name:               departmentUserItem.UserName,
			Type:               "user",
			TenantId:           departmentUserItem.TenantId,
			ParentId:           departmentUserItem.DepartmentId,
			WaitForReviewCount: tmpWaitForReviewCount,
			NotStartTaskCount:  tmpNotStartTaskCount,
			IpTaskCount:        tmpIPTaskCount,
			DoneTaskCount:      tmpDoneTaskCount,
			OverdueTaskCount:   tmpOverdueTaskCount,
			UserId:             departmentUserItem.UserId,
			Role:               departmentUserItem.RoleName,
		})
	}
	if len(departmentList) > 0 {
		sort.Sort(departmentList)
	}

	return
}

// FinanceStatistics 基于任务工时数据的财务分析 TODO
func (t *Task) FinanceStatistics(tenantId int64, projectIds []uint64, episodesIds []uint64, planEndTimeFrom uint64, planEndTimeTo uint64) (departmentList []DepartmentCountItem, err error) {

	return
}

// CountUnfinishedTaskWorkOrder 统计未完成工单数量，不需要限制时间，只要是未完成的一直显示
func (t *Task) CountUnfinishedTaskWorkOrder(tenantId int64, projectIds []uint64) (countNumber int64, err error) {
	dbr := ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Joins("JOIN task on task.id = task_work_order.task_id").
		Where("task.tenant_id = ?", tenantId).
		Where("task_work_order.status = ?", 0)

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Count(&countNumber).Error

	return
}

// CountCompletedTask 统计指定租户和项目和指定状态的任务数量，不需要限制时间
func (t *Task) CountTaskByStatus(tenantId int64, projectIds []uint64, ipStatusIds []string) (countNumber int64, err error) {

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json,'task_status') in ?", ipStatusIds)

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Count(&countNumber).Error

	return
}

// CountOverdueTask 统计指定租户和项目任务未完成且逾期的任务的任务数量
func (t *Task) CountUnfinishedAndOverdueTask(tenantId int64, projectIds []uint64, ipStatusIds []string) (countNumber int64, err error) {
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json,'task_status') in ?", ipStatusIds).
		Where("task.end_time <= ?", time.Now().Unix())

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Count(&countNumber).Error

	return
}

// 获取指定租户任务，不同项目制作数据情况
func getUserTaskCountMap(tenantId int64, projectIds []uint64, ipStatusIds []string, userIds []uint64) (userCountsMapRes map[uint64]uint64, projectCountMapRes map[uint64]uint64, err error) {
	var userDepartmentCounts []UserDepartmentCount

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json,'task_status') in ?", ipStatusIds).
		Where("task.executor in ?", userIds)

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Group("task.executor,task.project_id").
		Select("task.executor as user_id,task.project_id,count(task.id) AS count").
		Find(&userDepartmentCounts).Error

	if err != nil {
		return
	}

	// 统计用户负载
	// 1. 获取用户任务数量字典
	var userCountMap = make(map[uint64]uint64, 0)
	var projectCountMap = make(map[uint64]uint64, 0)
	for _, userItem := range userDepartmentCounts {
		if _, ok := userCountMap[userItem.UserId]; !ok {
			// 不存在
			userCountMap[userItem.UserId] = userItem.Count
		} else {
			// 存在
			userCountMap[userItem.UserId] += userItem.Count
		}

		if _, ok := projectCountMap[userItem.ProjectId]; !ok {
			// 不存在
			projectCountMap[userItem.ProjectId] = userItem.Count
		} else {
			// 存在
			projectCountMap[userItem.ProjectId] += userItem.Count
		}
	}

	// 2. 遍历统计用户负载
	for _, userId := range userIds {
		if _, ok := userCountMap[userId]; !ok {
			// 不存在
			userCountMap[userId] = 0
		}
	}

	// 2. 遍历统计项目负载
	for _, projectId := range projectIds {
		if _, ok := projectCountMap[projectId]; !ok {
			// 不存在
			projectCountMap[projectId] = 0
		}
	}

	return userCountMap, projectCountMap, err
}

// 获取指定租户任务，不同项目制作数据情况数量映射字典
// 2、返回有任务的人数
// 3、返回有任务的人总工时
func countUserTaskCountByUserIdsGroupByDepartmentIdUserId(tenantId int64, distinctUserIds []uint64, projectIds []uint64) (userManHourMapRes map[string]float64, userInvestmentMapRes map[string]uint64, err error) {
	if len(distinctUserIds) == 0 {
		return
	}
	var userDepartmentCounts []UserDepartmentManHourCount

	// 获取进行中任务状态列表
	var StatusService Status
	ipStatusIds, _ := StatusService.GetStatusIdsByCorrespond([]string{"feedback", "daily", "in_progress"})
	if len(ipStatusIds) == 0 {
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ?", tenantId).
		Where("task.executor in (?) ", distinctUserIds).
		Where("simpleJSONExtractUInt(task.json,'task_status') in ?", ipStatusIds)

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Group("task.executor,task.department_id").
		Select("task.executor as user_id,task.department_id,sum(toFloat64OrZero(task.man_hour)) AS count").
		Find(&userDepartmentCounts).Error

	if err != nil {
		return
	}

	// 生成 user_id 数据字典
	var userManHourMap = make(map[string]float64, 0)
	var userInvestmentMap = make(map[string]uint64, 0)
	for _, stepItem := range userDepartmentCounts {
		userManHourMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = stepItem.Count
		userInvestmentMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = 1
	}

	return userManHourMap, userInvestmentMap, err
}

// 按用户分组获取指定当前时间范围待交付任务数量映射字典
func countOverdueTaskByUserIdsGroupByDepartmentIdUserId(tenantId int64, distinctUserIds []uint64, projectIds []uint64) (userTaskVersionCountMap map[string]uint64, err error) {
	if len(distinctUserIds) == 0 {
		return
	}
	var userDepartmentCounts []UserDepartmentCount

	// 获取进行中任务状态列表
	var StatusService Status
	corresponds := []string{"feedback", "daily", "in_progress"}
	ipStatusIds, _ := StatusService.GetStatusIdsByCorrespond(corresponds)
	if len(ipStatusIds) == 0 {
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ?", tenantId).
		Where("task.executor in (?) ", distinctUserIds).
		Where("simpleJSONExtractUInt(task.json,'task_status') in ?", ipStatusIds).
		Where("task.plan_end_time <= ?", time.Now().Unix())

	if len(projectIds) > 0 {
		dbr.Where("task.project_id in ?", projectIds)
	}

	err = dbr.Group("task.executor,task.department_id").
		Select("task.executor as user_id,task.department_id,count(task.id) AS count").
		Find(&userDepartmentCounts).Error

	if err != nil {
		return
	}

	// 生成 user_id 数据字典
	var userIdMap = make(map[string]uint64, 0)
	for _, stepItem := range userDepartmentCounts {
		userIdMap[fmt.Sprintf("%d_%d", stepItem.UserId, stepItem.DepartmentId)] = stepItem.Count
	}

	userTaskVersionCountMap = userIdMap

	return
}

// GetUserWorkloadStatistics 获取用户任务负载情况，包含项目负载情况
func (t *Task) GetUserTaskWorkloadStatistics(tenantId int64, projectIds []uint64, ipStatusIds []string, userIds []uint64) (userTaskWorkloadRes UserTaskWorkloadRes, err error) {
	userCountsMap, projectCountMap, err := getUserTaskCountMap(tenantId, projectIds, ipStatusIds, userIds)

	// 用户负载计算数据
	var userTotalCount uint64
	var userInvestmentCount uint64
	for _, userCount := range userCountsMap {
		userTotalCount++
		if userCount > 0 {
			userInvestmentCount++
		}
	}
	userTaskWorkloadRes.UserCount = TaskWorkloadUserCount{
		Total:      userTotalCount,
		Investment: userInvestmentCount,
	}

	// 项目负载填充项目名称
	var projectService Project
	var projectTaskWorkloadRes []TaskWorkload
	projectNameMap, err := projectService.GetProjectNameMapByIds(projectIds)
	for projectId, projectCount := range projectCountMap {
		if _, ok := projectNameMap[projectId]; ok {
			if projectCount == 0 {
				continue
			}
			// 存在， 不存在项目排除
			projectTaskWorkloadRes = append(projectTaskWorkloadRes, TaskWorkload{
				Name:  projectNameMap[projectId],
				Count: projectCount,
			})
		}
	}
	userTaskWorkloadRes.ProjectCount = projectTaskWorkloadRes
	return
}

// GetTaskWorkloadStatistics 获取团队总产能负载数据
// 1、总产能，目前只能统计到部门人数
// 2、已投入，目前手上有任务组员人数
// 3、进行中任务工时总数
// 4、手上逾期任务个数
// 5、逾期任务个数
// 6、项目占比
func (t *Task) GetTaskWorkloadStatistics(tenantId int64, projectIds []uint64) (departmentList DepartmentWorkloadCountList, err error) {
	// 查询当前所属当前租户的所有部门
	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getDepartmentList(tenantId, nil)

	if err != nil {
		return
	}

	// 部门 ids
	var departmentIds []uint64
	for _, departmentItem := range departmentListQueryRes {
		departmentList = append(departmentList, DepartmentWorkloadCountItem{
			Id:                  departmentItem.Id,
			Name:                departmentItem.Name,
			Type:                "department",
			TenantId:            departmentItem.TenantId,
			ParentId:            departmentItem.ParentId,
			UserTotalCount:      0,
			UserInvestmentCount: 0,
			TaskManHour:         0,
			OverdueTaskCount:    0,
			UserId:              0,
			Role:                "",
		})
		departmentIds = append(departmentIds, departmentItem.Id)
	}

	// 获取指定租户下面所有用户及其角色标签
	var UserService User

	departmentUsers, distinctUserIds, departmentUserErr := UserService.GetDepartmentUserMultiDepartmentUser(departmentIds, tenantId)

	if departmentUserErr != nil {
		err = departmentUserErr
		return
	}

	// 按用户分组获取指定租户任务，不同项目制作数据情况数量映射字典
	userManHourMap, userInvestmentMap, err := countUserTaskCountByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, projectIds)

	if err != nil {
		return
	}

	// 按用户分组获取指定租户任务，不同项目逾期任务映射字典
	userOverdueTaskCountMap, err := countOverdueTaskByUserIdsGroupByDepartmentIdUserId(tenantId, distinctUserIds, projectIds)

	if err != nil {
		return
	}

	// 把用户提交版本数据回插入到部门
	var tmpUserInvestmentCount uint64
	var tmpTaskManHour float64
	var tmpOverdueTaskCount uint64

	var countKey string
	for _, departmentUserItem := range departmentUsers {
		tmpUserInvestmentCount = 0
		tmpTaskManHour = 0
		tmpOverdueTaskCount = 0

		countKey = fmt.Sprintf("%d_%d", departmentUserItem.UserId, departmentUserItem.DepartmentId)

		if _, ok := userManHourMap[countKey]; ok {
			tmpTaskManHour, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", userManHourMap[countKey]), 64)
		}

		if _, ok := userInvestmentMap[countKey]; ok {
			tmpUserInvestmentCount = userInvestmentMap[countKey]
		}

		if _, ok := userOverdueTaskCountMap[countKey]; ok {
			tmpOverdueTaskCount = userOverdueTaskCountMap[countKey]
		}

		departmentList = append(departmentList, DepartmentWorkloadCountItem{
			Id:                  0,
			Name:                departmentUserItem.UserName,
			Type:                "user",
			TenantId:            departmentUserItem.TenantId,
			ParentId:            departmentUserItem.DepartmentId,
			UserTotalCount:      1,
			UserInvestmentCount: tmpUserInvestmentCount,
			TaskManHour:         tmpTaskManHour,
			OverdueTaskCount:    tmpOverdueTaskCount,
			UserId:              departmentUserItem.UserId,
			Role:                departmentUserItem.RoleName,
		})
	}

	if len(departmentList) > 0 {
		sort.Sort(departmentList)
	}

	return
}

// GetWaitAssignStepCategoryCount 任务待分配分类统计
func (t *Task) GetWaitAssignStepCategoryCount(req types.GetWaitAssignStepCategoryCountReq) (res []types.StepCategoryWithCount, err error) {
	var taskFinalStatus = model.Status{}
	ClickHouse.DB.Model(&model.Status{}).Where("code = 'final'").Find(&taskFinalStatus)

	db := ClickHouse.DB.Table("entity e").Joins("JOIN task t on e.id = t.entity_id")
	if taskFinalStatus.Id > 0 {
		db = db.Where("simpleJSONExtractInt(t.json, 'task_status') <> ?", taskFinalStatus.Id)
	}
	if req.ProjectId > 0 {
		db = db.Where("e.project_id = ?", req.ProjectId)
	}
	if req.StepType != "" {
		db = db.Where("t.step_type = ?", req.StepType)
	}
	if len(req.AssignStatus) > 0 {
		db = db.Where("t.assign_status in ?", req.AssignStatus)
	}
	if len(req.StepCategoryId) > 0 {
		db = db.Where("t.step_category_id in ?", req.StepCategoryId)
	}
	if len(req.EpisodeShowingsMap) > 0 {
		dbs := ClickHouse.DB
		for _, showingsMap := range req.EpisodeShowingsMap {
			if len(showingsMap.ShowingsNumber) > 0 {
				dbs = dbs.Or("e.initial_episode_code = ? and e.showings_number in ?", showingsMap.EpisodeCode, showingsMap.ShowingsNumber)
			} else {
				dbs = dbs.Or("e.initial_episode_code = ?", showingsMap.EpisodeCode)
			}
		}
		db = db.Where(dbs)
	}

	var stepCategoryCount []struct {
		StepCategoryId uint64
		Count          uint64
	}
	err = db.Select("t.step_category_id as step_category_id,count(t.id) as count").Group("t.step_category_id").Find(&stepCategoryCount).Error
	if err != nil {
		return
	}
	var stepCategoryCountMap = make(map[uint64]uint64, 0)
	for _, s := range stepCategoryCount {
		stepCategoryCountMap[s.StepCategoryId] = s.Count
	}

	err = ClickHouse.DB.Model(&model.StepCategory{}).Where("id in ?", req.BaseCountStepCategoryList).Order("index ASC").Find(&res).Error
	if err != nil {
		return
	}
	for i, v := range res {
		if _, ok := stepCategoryCountMap[v.Id]; ok {
			res[i].WaitAssignCount = stepCategoryCountMap[v.Id]
		}
	}
	return
}

// getWaitUploadFileTodoCount 统计等待上传的文件记录
func (t *Task) getWaitUploadFileTodoCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	sub := ClickHouse.DB.Table("`order` o").
		Joins("JOIN order_task_file_list otfl on o.id = otfl.order_id "+
			"JOIN order_task_upstream_version otuv on otfl.task_id = otuv.task_id "+
			"JOIN version v on otuv.version_id = v.id "+
			"JOIN task on task.id = v.link_id").
		Where("otuv.from_tenant_id = ? and task.executor_type IN ('default', 'inside')", tenantId).
		Select("max(v.id) as id").Group("v.link_id")

	if len(req.ProjectIdList) > 0 {
		sub = sub.Where("v.project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, err := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if err != nil {
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		sub = sub.Where("v.project_id in ?", imUserProjectIds)
	}
	//else {
	//	saasUserId, errOccurred := GetSaasUserIdByUnionId(userId)
	//	if errOccurred != nil {
	//		err = errOccurred
	//		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
	//		return
	//	}
	//	projectIdList, errOccurred := model.GetUserProjectIdListByRoleCode(saasUserId, []string{}, []string{"in_progress"})
	//	if errOccurred != nil {
	//		err = errOccurred
	//		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
	//		return
	//	}
	//	if len(projectIdList) == 0 {
	//		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
	//		return
	//	}
	//	sub = sub.Where("v.project_id in ?", projectIdList)
	//}
	err = ClickHouse.DB.Table("(?) as vm", sub).
		Joins("JOIN version v on v.id = vm.id").
		Where("v.is_uploaded = 'no'").
		Group("v.project_id").
		Select("count(distinct v.id) as number,v.project_id as project_id").
		//Select("count(v.id) as number").
		Find(&res.CountItem.CountData).
		Error

	return
}

// getWaitDownloadFileCount 统计等待下载的文件记录
func (t *Task) getWaitDownloadFileCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	sub := ClickHouse.DB.Table("`order_task_file_list` otfl").
		Joins("JOIN order_task_upstream_version otuv on otfl.task_id = otuv.task_id and otfl.to_tenant_id = otuv.to_tenant_id "+
			"JOIN version v on otuv.version_id = v.id").
		Where("otfl.to_tenant_id = ? and v.is_uploaded = 'yes'", tenantId, tenantId).
		Select("max(otuv.id) as id").Group("v.link_id")

	if len(req.ProjectIdList) > 0 {
		sub = sub.Where("otfl.to_project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, err := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if err != nil {
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		sub = sub.Where("otfl.to_project_id in ?", imUserProjectIds)
	}
	//else {
	//	saasUserId, errOccurred := GetSaasUserIdByUnionId(userId)
	//	if errOccurred != nil {
	//		err = errOccurred
	//		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
	//		return
	//	}
	//	projectIdList, errOccurred := model.GetUserProjectIdListByRoleCode(saasUserId, []string{}, []string{"in_progress"})
	//	if errOccurred != nil {
	//		err = errOccurred
	//		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
	//		return
	//	}
	//	if len(projectIdList) == 0 {
	//		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
	//		return
	//	}
	//	sub = sub.Where("otfl.to_project_id in ?", projectIdList)
	//}

	err = ClickHouse.DB.Table("(?) as vm", sub).
		Joins("JOIN order_task_upstream_version dl on dl.id = vm.id " +
			"JOIN order_task_file_list otfl on otfl.to_tenant_id = dl.to_tenant_id and otfl.task_id = dl.task_id").
		Where("dl.is_download = 'no'").
		Group("otfl.to_project_id").
		Select("count(distinct dl.id) as number,otfl.to_project_id as project_id").
		//Select("count(distinct dl.id) as number").
		Find(&res.CountItem.CountData).
		Error
	return
}

// getWaitReviewCount 待送审
func (t *Task) getWaitReviewCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	db := ClickHouse.DB.Model(&model.Order{})
	if len(req.ProjectIdList) > 0 {
		db = db.Where("`order`.project_id in ?", req.ProjectIdList)
	}

	err = db.Joins("join order_task_list on `order`.id = order_task_list.order_id").
		Where("`order`.to_tenant_id = ? and order_task_list.deal_status = 'to_submit'", tenantId).
		Select("count(distinct `order`.id) as number").Find(&res.CountItem.CountData).Error
	return
}

// getWaitAcceptOrdersCount 待接受订单
func (t *Task) getWaitAcceptOrdersCount(tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	err = ClickHouse.DB.Model(&model.Order{}).
		Where("`order`.status ='wait_for_party_b_ensure' and `order`.to_tenant_id =?", tenantId).
		Select("count(`order`.id) as number").Find(&res.CountItem.CountData).Error
	return
}

// getWaitSignOrderContractCount 待签订订单合同
func (t *Task) getWaitSignOrderContractCount(tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	bm, err := t.getBusinessMode()
	if err != nil {
		return
	}
	db := ClickHouse.DB.Model(&model.Order{})
	switch bm {
	case "both_sides_mode":
		db.Joins("join contract c on c.order_id = `order`.id").Where("(`order`.from_tenant_id = ? and c.party_a_signed_time = 0) or (`order`.to_tenant_id = ? and c.party_b_signed_time = 0)", tenantId, tenantId)
		break
	case "tripartite_mode":
		db.Joins("join contract ac on ac.order_id = `order`.id and ac.to_tenant_id = `order`.from_tenant_id").
			Joins("join contract bc on bc.order_id = `order`.id and bc.to_tenant_id = `order`.to_tenant_id").
			Where("(`order`.from_tenant_id = ? and ac.party_b_signed_time = 0) or (`order`.to_tenant_id = ? and bc.party_b_signed_time = 0)", tenantId, tenantId)
		break
	}

	err = db.
		Where("`order`.status ='wait_for_sign' and (`order`.from_tenant_id = ? or `order`.to_tenant_id =?) ", tenantId, tenantId).
		Select("count(distinct `order`.id) as number").Find(&res.CountItem.CountData).Error
	return
}

// getBusinessMode 获得合同模式
func (t *Task) getBusinessMode() (BusinessMode string, err error) {
	err = ClickHouse.DB.Model(&model.ImOptions{}).Where("name = 'platform_tenant_settings'").
		Select("simpleJSONExtractString(`config`, 'business_mode') as business_mode").
		Find(&BusinessMode).Error
	return
}

// getWaitSignFrameworkContractCount 待签订框架合同
func (t *Task) getWaitSignFrameworkContractCount(tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	BusinessMode, err := t.getBusinessMode()
	if err != nil {
		return
	}

	db := ClickHouse.DB.Model(&model.Supplier{})

	dbs := ClickHouse.DB
	switch BusinessMode {
	case "both_sides_mode":
		dbs = dbs.Where("supplier.framework_agreement_status = 'wait_for_sign'")
		break
	case "tripartite_mode":
		dbs = dbs.Where("tenant.framework_agreement_status = 'wait_for_sign'")
		break
	}

	err = db.
		Joins("JOIN tenant_authentication ata ON ata.tenant_id = supplier.from_tenant_id").
		Joins("JOIN tenant_authentication bta ON bta.tenant_id = supplier.tenant_id").
		Where("ata.status = 'active' and bta.status = 'active' and ata.fdd_cert_status = '1' and ata.fdd_auth_status = '4' and bta.fdd_cert_status = '1' and bta.fdd_auth_status = '4'").
		Where("supplier.type = 'outside' and (supplier.from_tenant_id = ? or supplier.tenant_id = ?)", tenantId, tenantId).
		Where(dbs).
		Select("count(distinct supplier.id) as number").Find(&res.CountItem.CountData).Error

	return
}

// getOutsourcingReviewCount 外包审批
func (t *Task) getOutsourcingReviewCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	// im user -> saas user
	saasUserId, errOccurred := GetSaasUserIdByUnionId(userId)
	if errOccurred != nil {
		err = errOccurred
		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
		return
	}

	db := ClickHouse.DB.Model(&model.Order{}).
		Joins("join review_request on order.id = review_request.link_id").
		Joins("join review_request_node on review_request.id = review_request_node.review_request_id")

	var moduleModel = model.Module{}
	orderModule, err := moduleModel.GetByCode("order")
	if err != nil {
		return
	}

	if len(req.ProjectIdList) > 0 {
		db = db.Where("`order`.project_id in ?", req.ProjectIdList)
	}

	err = db.Where("`order`.from_tenant_id = ? and `order`.status = 'wait_for_party_a_review' and "+
		"review_request.module_id = ? and "+
		"review_request_node.plan_reviewer = ? and review_request_node.is_reviewed='no'", tenantId, orderModule.Id, saasUserId).
		Select("count(`order`.id) as number").Find(&res.CountItem.CountData).Error
	return
}

// getWaitExecRepairOrderCount 待处理工单
func (t *Task) getWaitExecRepairOrderCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	// im user -> saas user

	saasUserId, errOccurred := GetSaasUserIdByUnionId(userId)
	if errOccurred != nil {
		err = errOccurred
		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
		return
	}

	db := ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Joins("join task on task.id = task_work_order.task_id")

	status, err := model.GetStatusByCode("closed")
	if err != nil {
		return
	}
	if status.Id > 0 {
		db.Where("simpleJSONExtractUInt(task.json, 'task_status') != ?", status.Id)
	}

	if len(req.ProjectIdList) > 0 {
		db = db.Where("task.project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		db = db.Where("task.project_id in ?", imUserProjectIds)
	}

	//计划完成时间筛选
	if req.StartTime > 0 {
		db = db.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		db = db.Where("task.plan_end_time <= ?", req.EndTime)
	}

	//工单状态范围限制
	options := model.Options{}
	untreatedString, err := options.GetConfigItem("task_work_order_status_config", "untreated")
	if err != nil {
		return
	}
	untreatedStatus := strings.Split(untreatedString, ",")

	err = db.Where("task_work_order.tenant_id = ? and task_work_order.receiver =? and task_work_order.status in (?) and task.step_type = 'step'", tenantId, saasUserId, untreatedStatus).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Select("task.project_id as project_id,count(task_work_order.id) as number").Group("project_id").Find(&res.CountItem.CountData).Error
	return
}

// getNotExecRepairOrderCount 未处理工单
func (t *Task) getNotExecRepairOrderCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	saasUserId, errOccurred := GetSaasUserIdByUnionId(userId)
	if errOccurred != nil {
		err = errOccurred
		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
		return
	}

	db := ClickHouse.DB.Model(&model.TaskWorkOrder{}).
		Joins("join task on task.id = task_work_order.task_id")

	status, err := model.GetStatusByCode("closed")
	if err != nil {
		return
	}
	if status.Id > 0 {
		db.Where("simpleJSONExtractUInt(task.json, 'task_status') != ?", status.Id)
	}

	if len(req.ProjectIdList) > 0 {
		db = db.Where("task.project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		db = db.Where("task.project_id in ?", imUserProjectIds)
	}

	//计划完成时间筛选
	if req.StartTime > 0 {
		db = db.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		db = db.Where("task.plan_end_time <= ?", req.EndTime)
	}

	//工单状态范围限制
	options := model.Options{}
	untreatedString, err := options.GetConfigItem("task_work_order_status_config", "untreated")
	if err != nil {
		return
	}
	untreatedStatus := strings.Split(untreatedString, ",")

	err = db.Where("task_work_order.tenant_id = ? and task_work_order.created_by =? and task_work_order.status in (?) and task.step_type = 'step'", tenantId, saasUserId, untreatedStatus).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Select("task.project_id as project_id,count(task_work_order.id) as number").Group("project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWaitDepartmentTasksCount 待分配部门任务
func (t *Task) getWaitDepartmentTasksCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	db := ClickHouse.DB.Model(&model.Task{}).Joins("join entity on task.entity_id = entity.id")

	status, err := model.GetStatusByCode("closed")
	if err != nil {
		return
	}
	if status.Id > 0 {
		db.Where("simpleJSONExtractUInt(task.json, 'task_status') != ?", status.Id)
	}

	if len(req.ProjectIdList) > 0 {
		db = db.Where("task.project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		db = db.Where("task.project_id in ?", imUserProjectIds)
	}

	//计划完成时间筛选
	if req.StartTime > 0 {
		db = db.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		db = db.Where("task.plan_end_time <= ?", req.EndTime)
	}
	//任务模块范围限制

	var optionsConfig string

	err = ClickHouse.DB.Model(&model.Options{}).Where("name = 'merge_module_config'").Select("config").
		Find(&optionsConfig).Error

	if err != nil {
		return
	}

	var mergeModuleConfig map[string]string
	err = json.Unmarshal([]byte(optionsConfig), &mergeModuleConfig)

	if err != nil {
		return
	}

	var moduleIds []uint64
	if len(mergeModuleConfig) > 0 {
		var moduleCode []string
		for _, moduleScope := range mergeModuleConfig {
			moduleCode = append(moduleCode, moduleScope)
		}

		moduleCodeString := strings.Join(moduleCode, ",")
		moduleCode = strings.Split(moduleCodeString, ",")
		err = ClickHouse.DB.Model(&model.Module{}).Where("code in ?", moduleCode).Select("id").Pluck("id", &moduleIds).Error
		if err != nil {
			return
		}
	}

	if len(moduleIds) > 0 {
		db = db.Where("task.entity_module_id in ?", moduleIds)
	}

	err = db.
		Where("entity.is_generate = 'yes' and task.step_type = 'step' and task.assign_status = 'default' and task.tenant_id = ?", tenantId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Select("task.project_id as project_id,count(task.id) as number").Group("task.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWaitSubmitReviewCount 待提交审核
func (t *Task) getWaitSubmitReviewCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	db := ClickHouse.DB.Model(&model.Task{})

	if len(req.ProjectIdList) > 0 {
		db = db.Where("project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		db = db.Where("project_id in ?", imUserProjectIds)
	}

	status, err := model.GetStatusByCode("closed")
	if err != nil {
		return
	}
	if status.Id > 0 {
		db.Where("simpleJSONExtractUInt(task.json, 'task_status') != ?", status.Id)
	}

	//计划完成时间筛选
	if req.StartTime > 0 {
		db = db.Where("plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		db = db.Where("plan_end_time <= ?", req.EndTime)
	}

	//待提交审核状态
	dealStatus := []string{"to_submit"}

	err = db.Where("step_type = 'step' and deal_status in ?", dealStatus).
		Where("JSONLength(JSONExtractString(json, 'task_deprecate_data')) = 0").
		Select("project_id,COUNT(id) as number").Group("project_id").Find(&res.CountItem.CountData).Error
	return
}

// getWaitExecReviewCount 待处理审核任务
func (t *Task) getWaitExecReviewCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	db := ClickHouse.DB.Model(&model.Task{})

	if len(req.ProjectIdList) > 0 {
		db = db.Where("project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		db = db.Where("project_id in ?", imUserProjectIds)
	}

	status, err := model.GetStatusByCode("closed")
	if err != nil {
		return
	}
	if status.Id > 0 {
		db.Where("simpleJSONExtractUInt(json, 'task_status') != ?", status.Id)
	}

	//计划完成时间筛选string.
	if req.StartTime > 0 {
		db = db.Where("plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		db = db.Where("plan_end_time <= ?", req.EndTime)
	}

	//待提交审核状态
	dealStatus := []string{"pending", "processing"}

	err = db.Where("step_type = 'step' and deal_status in ?", dealStatus).
		Where("JSONLength(JSONExtractString(json, 'task_deprecate_data')) = 0").
		Select("project_id,COUNT(id) as number").Group("project_id").Find(&res.CountItem.CountData).Error
	return
}

// getBackReviewCount 返修审批
func (t *Task) getBackReviewCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	module := &model.Module{}
	taskRepairOrderModel, err := module.GetByCode("task_repair_order")

	if err != nil {
		return
	}

	db := ClickHouse.DB.Model(&model.ReviewRequest{}).
		Joins("JOIN task_repair_order on task_repair_order.id = review_request.link_id ").
		Joins("JOIN review_request_node on review_request.id = review_request_node.review_request_id")

	if len(req.ProjectIdList) > 0 {
		db = db.Where("task_repair_order.project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		db = db.Where("task_repair_order.project_id in ?", imUserProjectIds)
	}

	err = db.Where("review_request.tenant_id =?", tenantId).
		Where("review_request.module_id =?", taskRepairOrderModel.Id).
		Where("review_request_node.is_reviewed = 'no' AND review_request_node.is_active = 'yes'").Select("task_repair_order.project_id as project_id,COUNT(task_repair_order.id) as number").
		Group("task_repair_order.project_id").
		Find(&res.CountItem.CountData).Error

	return
}

// getWaitDispatchExecutorTaskCount 待分配执行人任务
func (t *Task) getWaitDispatchExecutorTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	// 待分配执行人任务(指组内代分配的任务)
	var DepartmentService Department
	departmentListQueryRes, departmentErr := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")
	if departmentErr != nil {
		return
	}
	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}
	if len(departmentId) == 0 {
		return
	}
	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}
	var taskStatusId []uint8
	for _, v := range status {
		if v.Code != "closed" {
			taskStatusId = append(taskStatusId, v.Id)
		}
	}

	codes := []string{"asset", "level", "sequence", "shot", "design"}
	var moduleIds []int64
	moduleModel := model.Module{}
	for _, v := range codes {
		moduleList, _ := moduleModel.GetByCode(v)
		moduleIds = append(moduleIds, moduleList.Id)
	}

	dbr := ClickHouse.DB.Model(&model.Entity{})
	dbr.Select("task.project_id as project_id,count(*) as number").
		Joins(" JOIN `task` ON `task`.entity_id = `entity`.id").
		Where("`entity`.tenant_id = ?", tenantId).
		Where("`entity`.module_id IN (?)", moduleIds).
		Where("`task`.department_id IN (?)", departmentId).
		Where("`task`.step_type = 'step'").
		Where("`task`.is_assignee = 'no'").
		Where("`task`.assign_status = 'department'").
		Where("simpleJSONExtractUInt(`task`.json, 'task_status') IN (?)", taskStatusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	err = dbr.Group("`task`.project_id").Find(&res.CountItem.CountData).Error
	return
}

// getCompletedTaskCount 已完成任务
func (t *Task) getCompletedTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).
		Where("code = 'final'").Find(&status).Error
	if err != nil {
		return
	}

	saasUserId, err := GetSaasUserIdByUnionId(userId)
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') = ?", status.Id).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	return
}

// getGroupAbnormalTaskCount 组内异常任务
func (t *Task) getGroupAbnormalTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Where("code !='closed'").Find(&status).Error
	if err != nil {
		return
	}
	var statusId []uint8
	for _, status := range status {
		statusId = append(statusId, status.Id)
	}

	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")

	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}
	if len(departmentId) == 0 {
		err = errors.New("departmentId not find")
		return
	}
	//工单状态范围限制
	options := model.Options{}
	untreatedString, err := options.GetConfigItem("task_work_order_status_config", "untreated")
	if err != nil {
		return
	}
	untreatedStatus := strings.Split(untreatedString, ",")

	var taskWorkList []struct {
		TaskId uint32
	}
	err = ClickHouse.DB.Select("task_work_order.task_id as task_id").
		Model(&model.TaskWorkOrder{}).
		Where("type = 'task_making'").
		Where("tenant_id = ?", tenantId).
		Where("status in (?)", untreatedStatus).Group("task_work_order.task_id").Find(&taskWorkList).Error

	if len(taskWorkList) == 0 {
		return
	}
	if err != nil {
		return
	}
	var taskId []uint32
	for _, v := range taskWorkList {
		taskId = append(taskId, v.TaskId)
	}
	dbr := ClickHouse.DB.Model(&model.Task{})
	dbr.Select("task.project_id as project_id,count(*) as number").
		Where("task.step_type = 'step'").
		Where("task.id in (?)", taskId).
		Where("simpleJSONExtractUInt(`task`.json, 'task_status') IN (?)", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 && req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime).Where("task.plan_end_time >= ?", req.StartTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Where("task.department_id IN ?", departmentId)
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getGroupNotStartTaskCount 组内未开始任务
func (t *Task) getGroupNotStartTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")

	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}
	if len(departmentId) == 0 {
		err = errors.New("departmentId not find")
		return
	}
	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}
	statusList := make(map[string]uint8, 0)
	var taskStatusId []uint8
	for _, status := range status {
		statusList[status.Code] = status.Id
		if status.Code != "closed" {
			taskStatusId = append(taskStatusId, status.Id)
		}
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("task.step_type = 'step'").
		Where("task.department_id IN ?", departmentId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') in (?)", []uint8{statusList["not_started"], statusList["wait_assemble"], statusList["ready_to_start"]}).
		Where("simpleJSONExtractUInt(task.json, 'task_status') IN (?)", taskStatusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.tenant_id = ?", tenantId)
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getGroupInProgressTaskCount 组内进行中任务
func (t *Task) getGroupInProgressTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}

	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}

	statusList := make(map[string]uint8, 0)
	for _, status := range status {
		statusList[status.Code] = status.Id
	}

	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")

	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}

	if len(departmentId) == 0 {
		err = errors.New("departmentId not find")
		return
	}

	// 获取制作待办,组内待办 进行中、审核中配置项
	var optionsConfig string
	err = ClickHouse.DB.Model(&model.Options{}).Where("name = 'group_making_wait_todo'").Select("config").
		Find(&optionsConfig).Error

	if optionsConfig == "" {
		err = errors.New("config not find")
		return
	}

	var groupInProgressConfig map[string]string
	err = json.Unmarshal([]byte(optionsConfig), &groupInProgressConfig)
	if err != nil {
		return
	}

	// 组内待办配置项
	var statusId []uint8
	groupInProgressCodeList := strings.Split(groupInProgressConfig["group_inprogress"], ",")
	for _, v := range groupInProgressCodeList {
		statusId = append(statusId, statusList[v])
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("simpleJSONExtractUInt(task.json, 'task_status') in (?)", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.tenant_id = ?", tenantId).
		Where("task.department_id IN ?", departmentId)
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getGroupSubmittedTaskCount 组内审核中任务
func (t *Task) getGroupSubmittedTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}

	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}

	statusList := make(map[string]uint8, 0)
	for _, status := range status {
		statusList[status.Code] = status.Id
	}

	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")

	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}
	if len(departmentId) == 0 {
		err = errors.New("departmentId not find")
		return
	}
	// 获取制作待办,组内待办 进行中、审核中配置项
	var optionsConfig string
	err = ClickHouse.DB.Model(&model.Options{}).Where("name = 'group_making_wait_todo'").Select("config").
		Find(&optionsConfig).Error

	if optionsConfig == "" {
		err = errors.New("config not find")
		return
	}

	var groupSubmittedConfig map[string]string
	err = json.Unmarshal([]byte(optionsConfig), &groupSubmittedConfig)
	if err != nil {
		return
	}

	// 组内审核中
	var statusId []uint8
	groupSubmittedCodeList := strings.Split(groupSubmittedConfig["group_submitted"], ",")
	for _, v := range groupSubmittedCodeList {
		statusId = append(statusId, statusList[v])
	}
	if len(statusId) == 0 {
		return
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("simpleJSONExtractUInt(task.json, 'task_status') IN (?)", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.tenant_id = ?", tenantId).
		Where("task.department_id IN ?", departmentId)
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getGroupFinalTaskCount 组内已完成任务
func (t *Task) getGroupFinalTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	//
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status struct {
		Id   int64  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).
		Where("code = 'final'").Find(&status).Error
	if err != nil {
		return
	}

	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")
	if err != nil {
		return
	}
	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}
	if len(departmentId) == 0 {
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("simpleJSONExtractUInt(task.json, 'task_status') = ?", status.Id).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("task.tenant_id = ?", tenantId).
		Where("task.department_id IN ?", departmentId)
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getGroupOverdueTaskCount 组内逾期任务数
func (t *Task) getGroupOverdueTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Where("code not in ('final','closed')").Find(&status).Error
	if err != nil {
		return
	}

	var statusId []uint8
	for _, status := range status {
		statusId = append(statusId, status.Id)
	}
	var DepartmentService Department
	departmentListQueryRes, err := DepartmentService.getUserDepartmentList(userId, tenantId, "headman")
	if err != nil {
		return
	}
	var departmentId []int64
	for _, v := range departmentListQueryRes {
		departmentId = append(departmentId, v.DepartmentId)
	}
	if len(departmentId) == 0 {
		err = errors.New("departmentId not find")
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("task.tenant_id = ?", tenantId).
		Where("task.department_id IN ?", departmentId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') IN (?)", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	} else {
		dbr.Where("task.plan_end_time <= ?", time.Now().Unix())
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getAbnormalTaskCount 异常任务
func (t *Task) getAbnormalTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}

	var taskStatusId []uint8
	for _, v := range status {
		if v.Code != "closed" {
			taskStatusId = append(taskStatusId, v.Id)
		}
	}

	var taskWorkList []struct {
		TaskId uint32
	}

	//工单状态范围限制
	options := model.Options{}
	untreatedString, err := options.GetConfigItem("task_work_order_status_config", "untreated")
	if err != nil {
		return
	}
	untreatedStatus := strings.Split(untreatedString, ",")

	err = ClickHouse.DB.Select("task_work_order.task_id as task_id").
		Model(&model.TaskWorkOrder{}).Where("status in (?)", untreatedStatus).
		Where("type = 'task_making'").
		Where("tenant_id = ?", tenantId).
		Group("task_work_order.task_id").Find(&taskWorkList).Error
	if err != nil {
		return
	}
	if len(taskWorkList) == 0 {
		return
	}
	var taskId []uint32
	for _, v := range taskWorkList {
		taskId = append(taskId, v.TaskId)
	}

	saasUserId, saasErr := GetSaasUserIdByUnionId(userId)
	if saasErr != nil {
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{})
	dbr.Select("task.project_id as project_id,count(*) as number").
		Where("task.executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("task.id IN (?)", taskId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') IN (?)", taskStatusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getNotStartTaskTaskCount 未开始任务
func (t *Task) getNotStartTaskTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}
	statusList := make(map[string]uint8, 0)
	for _, v := range status {
		statusList[v.Code] = v.Id
	}

	saasUserId, err := GetSaasUserIdByUnionId(userId)
	if err != nil {
		return
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') in (?)", []uint8{statusList["not_started"], statusList["wait_assemble"], statusList["ready_to_start"]}).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getInProgressTaskCount 进行中任务
func (t *Task) getInProgressTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error

	if err != nil {
		return
	}

	statusList := make(map[string]uint8, 0)
	for _, v := range status {
		statusList[v.Code] = v.Id
	}

	saasUserId, err := GetSaasUserIdByUnionId(userId)
	if err != nil {
		return
	}

	// 获取制作待办,组内待办 进行中、审核中配置项
	var optionsConfig string
	err = ClickHouse.DB.Model(&model.Options{}).Where("name = 'group_making_wait_todo'").Select("config").
		Find(&optionsConfig).Error

	if optionsConfig == "" {
		err = errors.New("config not find")
		return
	}

	var makingInProgressConfig map[string]string
	err = json.Unmarshal([]byte(optionsConfig), &makingInProgressConfig)
	if err != nil {
		return
	}

	// 进行中
	var statusId []uint8
	makingInProgressCodeList := strings.Split(makingInProgressConfig["making_inprogress"], ",")
	for _, v := range makingInProgressCodeList {
		statusId = append(statusId, statusList[v])
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') in (?)", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getSubmittedTaskCount 审核中任务
func (t *Task) getSubmittedTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error

	if err != nil {
		return
	}

	statusList := make(map[string]uint8, 0)
	for _, status := range status {
		statusList[status.Code] = status.Id
	}

	saasUserId, err := GetSaasUserIdByUnionId(userId)
	if err != nil {
		return
	}

	// 获取制作待办,组内待办 进行中、审核中配置项
	var optionsConfig string
	err = ClickHouse.DB.Model(&model.Options{}).Where("name = 'group_making_wait_todo'").Select("config").
		Find(&optionsConfig).Error

	if optionsConfig == "" {
		err = errors.New("config not find")
		return
	}

	var makingSubmittedConfig map[string]string
	err = json.Unmarshal([]byte(optionsConfig), &makingSubmittedConfig)
	if err != nil {
		return
	}

	// 进行中
	var statusId []uint8
	makingSubmittedCodeList := strings.Split(makingSubmittedConfig["making_submitted"], ",")
	for _, v := range makingSubmittedCodeList {
		statusId = append(statusId, statusList[v])
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Where("executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') IN (?)", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getOverdueTaskCount 逾期任务数
func (t *Task) getOverdueTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	var statusId []uint8
	for _, v := range status {
		if v.Code != "final" && v.Code != "closed" {
			statusId = append(statusId, v.Id)
		}
	}
	if err != nil {
		return
	}

	saasUserId, err := GetSaasUserIdByUnionId(userId)
	if err != nil {
		return
	}

	dbr := ClickHouse.DB.Model(&model.Task{}).
		Select("count(*) as number,task.project_id as project_id").
		Select("count(*) as number,task.project_id as project_id").
		Where("executor = ?", saasUserId).
		Where("task.step_type = 'step'").
		Where("task.executor_tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') IN (?)", statusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")
	if req.StartTime > 0 {
		dbr.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime)
	} else {
		dbr.Where("task.plan_end_time <= ?", time.Now().Unix())
	}
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getOverdueTodoTaskCount 逾期待办数
func (t *Task) getOverdueTodoTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()
	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	statusList := make(map[string]uint8, 0)
	for _, status := range status {
		statusList[status.Code] = status.Id
	}
	if err != nil {
		return
	}
	saasUserId, saasUseErr := GetSaasUserIdByUnionId(userId)
	if saasUseErr != nil {
		return
	}
	moduleModel := model.Module{}
	todoModule, todoModuleErr := moduleModel.GetByCode("todo")
	if todoModuleErr != nil {
		return
	}
	dbr := ClickHouse.DB.Model(&model.TaskMember{}).
		Select("count(*) as number,task.project_id as project_id").
		Joins("JOIN task on task_member.task_id = task.id").
		Where("task_member.user_id = ?", saasUserId).
		Where("task.entity_module_id = ?", todoModule.Id).
		Where("task.plan_end_time <> 0 and task.plan_end_time <= ?", time.Now().Unix()).
		Where("task.tenant_id =  ?", tenantId).
		Where("((task_member.user_id = ? OR task_member.partner = ?) AND"+
			" task_member.status = ?) OR (task.created_by = ? AND "+
			"simpleJSONExtractUInt(task.json, 'task_status') = ?)", saasUserId, saasUserId, statusList["not_started"], saasUserId, statusList["not_started"])
	if len(req.ProjectIdList) > 0 {
		dbr.Where("task.project_id IN (?)", req.ProjectIdList)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getUpstreamUpdateTaskCount 上游有更新的任务
func (t *Task) getUpstreamUpdateTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	res.CountItem.CountData = []types.TodoTaskOverviewResItemCountData{{ProjectId: 0, Number: 0}}
	return
}

// getWaitAbnormalTaskCount 待处理异常任务
func (t *Task) getWaitAbnormalTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}
	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}

	statusList := make(map[string]uint8, 0)
	for _, status := range status {
		statusList[status.Code] = status.Id
	}
	var taskStatusId []uint8
	for _, status := range status {
		if status.Code != "final" && status.Code != "closed" {
			taskStatusId = append(taskStatusId, status.Id)
		}
	}

	saasUserId, err := GetSaasUserIdByUnionId(userId)
	if err != nil {
		return
	}
	moduleModel := model.Module{}
	todoModule, err := moduleModel.GetByCode("todo")
	if err != nil {
		return
	}
	dbr := ClickHouse.DB.Model(&model.Task{})
	dbr.Select("task.project_id as project_id,count(*) as number").
		Joins("JOIN task_member tm on task.id = tm.task_id").
		Where("tm.user_id = ?", saasUserId).
		Where("task.step_type='work_order_to_do'").
		Where("task.entity_module_id = ?", todoModule.Id).
		Where("task.tenant_id =  ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') IN (?)", taskStatusId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Where("((task_member.user_id = ? OR task_member.partner = ?) AND"+
			" task_member.status = ?) OR (task.created_by = ? AND "+
			"simpleJSONExtractUInt(task.json, 'task_status') = ?)", saasUserId, saasUserId, statusList["not_started"], saasUserId, statusList["not_started"])
	if req.StartTime > 0 && req.EndTime > 0 {
		dbr.Where("task.plan_end_time <= ?", req.EndTime).Where("task.plan_end_time >= ?", req.StartTime)
	}
	if len(req.ProjectIdList) > 0 {
		var ids []uint64
		for _, v := range req.ProjectIdList {
			ids = append(ids, v)
		}
		dbr.Where("task.project_id IN (?)", ids)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("task.project_id in ?", imUserProjectIds)
	}
	dbr.Group("task.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// getOutsourcingFileAcceptanceCount 外包文件验收
func (t *Task) getOutsourcingFileAcceptanceCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	var status []struct {
		Id   uint8  `json:"id"`
		Code string `json:"code"`
	}

	err = ClickHouse.DB.Model(&model.Status{}).Find(&status).Error
	if err != nil {
		return
	}

	saasUserId, saasUserErr := GetSaasUserIdByUnionId(userId)
	if saasUserErr != nil {
		return
	}

	moduleModel := model.Module{}
	todoModuleModule, todoModuleErr := moduleModel.GetByCode("settlement")
	if todoModuleErr != nil {
		return
	}

	dbr := ClickHouse.DB.Model(&model.ReviewRequest{})
	dbr.Select("settlement.project_id as project_id,count(*) as number").
		Joins("JOIN settlement s on review_request.link_id = s.id").
		Joins("JOIN review_request_node rrn on review_request.id = rrn.review_request_id").
		Where("rrn.plan_reviewer = ?", saasUserId).
		Where("review_request.tenant_id = ?", tenantId).
		Where("rrn.is_reviewed = 'no'").
		Where("rrn.is_active = 'yes'").
		Where("review_request.module_id = ?", todoModuleModule.Id)
	if len(req.ProjectIdList) > 0 {
		dbr.Where("settlement.project_id IN (?)", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetImUserProjectIdListByRoleCode(userId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		dbr.Where("settlement.project_id in ?", imUserProjectIds)
	}
	dbr.Group("settlement.project_id").Find(&res.CountItem.CountData)
	err = dbr.Error
	return
}

// TodoTaskOverview 统计待办概览
func (t *Task) TodoTaskOverview(req types.TodoTaskOverviewReq, userId, tenantId int64) (res types.TodoTaskOverviewRes, err error) {

	// todo 测试通道阻塞问题
	resChan := make(chan types.TodoTaskOverviewResItemAsync, len(req.QueryCodeList))
	for _, v := range req.QueryCodeList {
		switch v {
		case "wait_upload_file": // 待上传文件
			go t.getWaitUploadFileTodoCount(req, userId, tenantId, resChan, v)
			break
		case "wait_download_file": // 待下载
			go t.getWaitDownloadFileCount(req, userId, tenantId, resChan, v)
			break
		case "wait_review": // 待送审
			go t.getWaitReviewCount(req, userId, tenantId, resChan, v)
			break
		case "wait_accept_orders": // 待接受订单
			go t.getWaitAcceptOrdersCount(tenantId, resChan, v)
			break
		case "wait_sign_order_contract": // 待签订订单合同
			go t.getWaitSignOrderContractCount(tenantId, resChan, v)
			break
		case "wait_sign_framework_contract": // 待签订框架合同
			go t.getWaitSignFrameworkContractCount(tenantId, resChan, v)
			break
		case "outsourcing_review": // 外包审批
			go t.getOutsourcingReviewCount(req, userId, tenantId, resChan, v)
			break
		case "wait_exec_repair_order": // 待处理工单
			go t.getWaitExecRepairOrderCount(req, userId, tenantId, resChan, v)
			break
		case "not_exec_repair_order": // 未处理工单
			go t.getNotExecRepairOrderCount(req, userId, tenantId, resChan, v)
			break
		case "wait_department_tasks": // 待分配部门任务
			go t.getWaitDepartmentTasksCount(req, userId, tenantId, resChan, v)
			break
		case "wait_submit_review": // 待提交审核
			go t.getWaitSubmitReviewCount(req, userId, tenantId, resChan, v)
			break
		case "wait_exec_review_count": // 待处理审核任务
			go t.getWaitExecReviewCount(req, userId, tenantId, resChan, v)
			break
		case "back_review": // 返修审批
			go t.getBackReviewCount(req, userId, tenantId, resChan, v)
			break
		case "wait_dispatch_executor_task": // 待分配执行人任务
			go t.getWaitDispatchExecutorTaskCount(req, userId, tenantId, resChan, v)
			break
		case "group_abnormal_task": // 组内异常任务
			go t.getGroupAbnormalTaskCount(req, userId, tenantId, resChan, v)
			break
		case "group_not_start_task": // 组内未开始任务
			go t.getGroupNotStartTaskCount(req, userId, tenantId, resChan, v)
			break
		case "group_in_progress_task": // 组内进行中任务
			go t.getGroupInProgressTaskCount(req, userId, tenantId, resChan, v)
			break
		case "group_submitted_task": // 组内审核中任务
			go t.getGroupSubmittedTaskCount(req, userId, tenantId, resChan, v)
			break
		case "group_final_task": // 组内已完成任务
			go t.getGroupFinalTaskCount(req, userId, tenantId, resChan, v)
			break
		case "group_overdue_task": // 组内逾期任务数
			go t.getGroupOverdueTaskCount(req, userId, tenantId, resChan, v)
			break
		case "abnormal_task": // 异常任务
			go t.getAbnormalTaskCount(req, userId, tenantId, resChan, v)
			break
		case "not_start_task": // 未开始任务
			go t.getNotStartTaskTaskCount(req, userId, tenantId, resChan, v)
			break
		case "in_progress_task": // 进行中任务
			go t.getInProgressTaskCount(req, userId, tenantId, resChan, v)
			break
		case "submitted_task": // 审核中任务
			go t.getSubmittedTaskCount(req, userId, tenantId, resChan, v)
			break
		case "completed_task": // 已完成任务
			go t.getCompletedTaskCount(req, userId, tenantId, resChan, v)
			break
		case "overdue_task": // 逾期任务数
			go t.getOverdueTaskCount(req, userId, tenantId, resChan, v)
			break
		case "overdue_todo_task": // 逾期待办数
			go t.getOverdueTodoTaskCount(req, userId, tenantId, resChan, v)
			break
		case "upstream_update_task": // 上游有更新的任务
			go t.getUpstreamUpdateTaskCount(req, userId, tenantId, resChan, v)
			break
		case "wait_abnormal_task": // 待处理异常任务
			go t.getWaitAbnormalTaskCount(req, userId, tenantId, resChan, v)
			break
		case "outsourcing_file_acceptance": // 外包文件验收
			go t.getOutsourcingFileAcceptanceCount(req, userId, tenantId, resChan, v)
			break
		case "repair_task": // 返修状态任务
			go t.getRepairTaskCount(req, userId, tenantId, resChan, v)
			break
		default:
			resChan <- types.TodoTaskOverviewResItemAsync{
				CountItem: types.TodoTaskOverviewResItem{
					Code: v,
				},
			}
		}

	}
	for range req.QueryCodeList {
		resItem := <-resChan
		if resItem.Err != nil {
			// 错误日志记录
			log.Println(resItem.CountItem.Code, resItem.Err.Error())
		}
		res = append(res, resItem.CountItem)
	}

	return
}

// CheckShotTaskDone 检查镜头任务是否满足全部完成
func (t *Task) CheckShotTaskDone(projectId int64, episodeId int64, scList []string, seqList []string) (res types.CheckShotTaskDoneRes, err error) {
	if len(scList) == 0 && len(seqList) == 0 {
		return
	}

	var episode model.Entity
	err = ClickHouse.DB.Model(&model.Entity{}).Where("id = ?", episodeId).Find(&episode).Error
	if err != nil {
		return
	}
	var sequenceIdList []uint64
	var seqIdMap = make(map[uint64]string, 0)

	var scNotMatchMap = make(map[string]bool)
	var sqNotMatchMap = make(map[string]bool)

	var checkStepCode string
	err = ClickHouse.DB.Table("options").
		Where("name = 'clip_config'").
		Pluck("simpleJSONExtractString(config,'upstream_step')", &checkStepCode).Error
	if err != nil {
		return
	}
	var checkStepId []int64
	if checkStepCode == "" {
		res.SeqList = seqList
		res.ScList = scList
		return
	} else {
		err = ClickHouse.DB.Table("step").
			Where("code in ?", strings.Split(checkStepCode, ",")).
			Pluck("id", &checkStepId).Error
		if err != nil {
			return
		}
		if len(checkStepId) == 0 {
			res.SeqList = seqList
			res.ScList = scList
			return
		}
	}

	db := ClickHouse.DB.Table("entity e").
		Joins("JOIN task t on e.id = t.entity_id").
		Where("JSONLength(JSONExtractString(t.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(t.json, 'task_status') in (select id from status where code not in ('final', 'ready_to_publish'))").               // 状态筛选
		Where("e.project_id = ? and e.module_id = (select id from module where code = ? limit 1) and e.initial_episode_code = ? and t.step_type = 'step' and t.step_id in ?", projectId, "shot", episode.Code, checkStepId) //项目,集,工序筛选
	extraWhere := ClickHouse.DB

	whereFlag := 0
	if len(scList) > 0 {
		extraWhere = extraWhere.Where("e.showings_number in ?", scList)
		whereFlag += 1
	}
	if len(seqList) > 0 {
		var seqQueryList []model.Entity
		err = ClickHouse.DB.Model(&model.Entity{}).Where("project_id = ? and module_id = (select id from module where code = 'sequence' limit 1) and initial_episode_code = ? and name in ?", projectId, episode.Code, seqList).Find(&seqQueryList).Error
		if err != nil {
			return
		}
		for _, entity := range seqQueryList {
			sequenceIdList = append(sequenceIdList, entity.Id)
			seqIdMap[entity.Id] = entity.Name
		}
		// 序列不存在处理
		if len(sequenceIdList) > 0 {
			if whereFlag > 0 {
				extraWhere = extraWhere.Or("e.entity_id in ?", sequenceIdList)
			} else {
				extraWhere = extraWhere.Where("e.entity_id in ?", sequenceIdList)
			}
		}
	}

	type scSeqMarkList struct {
		SequenceId     uint64
		ShowingsNumber string
	}
	var scSeqMarkListRes []scSeqMarkList
	err = db.Where(extraWhere).
		Select("e.entity_id as sequence_id,e.showings_number as showings_number").
		Group("e.entity_id,e.showings_number").
		Find(&scSeqMarkListRes).Error
	for _, re := range scSeqMarkListRes {

		scNotMatchMap[strings.ToUpper(re.ShowingsNumber)] = true
		if _, ok := seqIdMap[re.SequenceId]; ok {
			sqNotMatchMap[seqIdMap[re.SequenceId]] = true
		}
	}

	for _, sc := range scList {
		if _, ok := scNotMatchMap[strings.ToUpper(sc)]; !ok {
			res.ScList = append(res.ScList, sc)
		}
	}

	for _, seq := range seqList {
		if _, ok := sqNotMatchMap[seq]; !ok {
			res.SeqList = append(res.SeqList, seq)
		}
	}
	return
}

// 获得实际在用的工序
func (t *Task) getCountStepIdList() (res []model.Step, err error) {
	err = ClickHouse.DB.Model(&model.Step{}).
		Where("step_category_id in ("+
			"select id from step_category where parent_id in  (select id from step_category where entity_id in (select id from module where code in ?)))", []string{"design", "asset", "level", "sequence", "shot"}).
		Find(&res).Error

	return
}

// 生成剧集筛选条件
func BuildEpisodeShowingsNumberWhere(filter []types.EpisodeShowingsItem) interface{} {
	res := ClickHouse.DB
	for _, item := range filter {
		if len(item.ShowingsNumberList) > 0 {
			for i, s := range item.ShowingsNumberList {
				item.ShowingsNumberList[i] = strings.ToLower(s)
			}
			res = res.Or("entity.initial_episode_code = ? and lower(entity.showings_number) in ?", item.EpisodeCode, item.ShowingsNumberList)
		} else {
			res = res.Or("entity.initial_episode_code = ?", item.EpisodeCode)
		}
	}
	return res
}

// TaskSurveyByDepartment 任务概览 工单统计 通过部门
func (t *Task) TaskSurveyByDepartment(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdList, stepIdList []int64) (res []types.TaskSurveyRes, err error) {

	// 查询 未开始,进行中,待审核,已完成
	var taskGroupByDepartmentRes []types.TaskCountGroupByDepartmentTaskStatus

	query := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN status on status.id = simpleJSONExtractInt(task.json, 'task_status')").
		Where("task.tenant_id = ? and task.step_type = 'step' and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3", tenantId)
	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}
	if len(stepIdList) > 0 {
		query.Where("task.step_id in ?", stepIdList)
	}
	if len(stepCategoryIdList) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdList)
	}
	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}
	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = task.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}
	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	query.
		Select("task.department_id as department_id," +
			"task.executor      as executor," +
			"multiIf(status.id in (1, 2, 18), 'not_started'," +
			"status.id in (4, 6), 'in_progress'," +
			"status.id in (5, 9, 19), 'wait_review'," +
			"status.id in (7, 8), 'done', 'default') as status_text")

	err = ClickHouse.DB.Table("(?) as t", query).
		Select("department_id, executor, status_text, count(*) as count").
		Group("department_id, executor, status_text").
		Find(&taskGroupByDepartmentRes).Error

	fmt.Println(taskGroupByDepartmentRes)

	if err != nil {
		return
	}

	var userIdList = make([]int64, 0)
	var userMap = make(map[int64]bool)
	var queryDepartmentIdList = make([]int64, 0)
	var departmentMap = make(map[int64]bool)
	taskGroupByDepartmentCountMap := make(map[string]types.TaskCountGroupByDepartmentTaskStatus, 0)

	for _, re := range taskGroupByDepartmentRes {
		taskGroupByDepartmentCountMap[fmt.Sprintf("%d-%d-%s", re.DepartmentId, re.Executor, re.StatusText)] = re
		if _, ok := userMap[re.Executor]; !ok {
			userMap[re.Executor] = true
			userIdList = append(userIdList, re.Executor)
		}

		if _, ok := departmentMap[re.DepartmentId]; !ok {
			departmentMap[re.DepartmentId] = true
			queryDepartmentIdList = append(queryDepartmentIdList, re.DepartmentId)
		}
	}
	// 未分配任务统计
	departmentNotAssignTaskCount, err := t.getDepartmentNotAssignTaskCount(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdList, stepIdList)
	var departmentNotAssignTaskCountMap = make(map[int64]int64, 0)
	for _, count := range departmentNotAssignTaskCount {
		departmentNotAssignTaskCountMap[count.DepartmentId] = count.Count
		if _, ok := departmentMap[count.DepartmentId]; !ok {
			departmentMap[count.DepartmentId] = true
			queryDepartmentIdList = append(queryDepartmentIdList, count.DepartmentId)
		}
	}

	// 逾期任务统计
	overdueTaskCount, err := t.getDepartmentOverdueTaskCount(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdList, stepIdList)
	overdueTaskUserCountMap := make(map[string]types.DepartmentOverdueTaskCount, 0)
	overdueTaskDepartmentCountMap := make(map[int64]int64, 0)

	for _, count := range overdueTaskCount {
		if _, ok := departmentMap[count.DepartmentId]; !ok {
			departmentMap[count.DepartmentId] = true
			queryDepartmentIdList = append(queryDepartmentIdList, count.DepartmentId)
		}

		if _, ok := userMap[count.Executor]; !ok {
			userMap[count.Executor] = true
			userIdList = append(userIdList, count.Executor)
		}

		if count.Executor == 0 {
			if _, ok := overdueTaskDepartmentCountMap[count.DepartmentId]; !ok {
				overdueTaskDepartmentCountMap[count.DepartmentId] = count.Count
			} else {
				overdueTaskDepartmentCountMap[count.DepartmentId] += count.Count
			}
		} else {
			var tempKey = fmt.Sprintf("%d-%d", count.DepartmentId, count.Executor)
			if _, ok := overdueTaskUserCountMap[tempKey]; !ok {
				overdueTaskUserCountMap[tempKey] = count
			} else {
				overdueTaskUserCountMap[tempKey] = types.DepartmentOverdueTaskCount{
					DepartmentId: count.DepartmentId,
					Executor:     count.Executor,
					Count:        count.Count + count.Count,
				}
			}
		}
	}

	// 废除任务统计
	deprecatedTaskCount, err := t.getDepartmentDeprecatedTaskCount(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdList, stepIdList)
	if err != nil {
		return
	}
	deprecatedTaskCountMap := make(map[string]types.DepartmentDeprecatedTaskCount)
	for _, count := range deprecatedTaskCount {
		if _, ok := departmentMap[count.DepartmentId]; !ok {
			departmentMap[count.DepartmentId] = true
			queryDepartmentIdList = append(queryDepartmentIdList, count.DepartmentId)
		}
		if _, ok := userMap[count.Executor]; !ok {
			userMap[count.Executor] = true
			userIdList = append(userIdList, count.Executor)
		}
		var tempKey = fmt.Sprintf("%d-%d", count.DepartmentId, count.Executor)
		deprecatedTaskCountMap[tempKey] = count
	}

	// 工单统计
	taskWorkOrderCount, err := t.getTaskWorkOrderCountByDepartmentUser(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdList, stepIdList)
	if err != nil {
		return
	}
	taskWorkOrderUserCountMap := make(map[string]types.TaskWorkOrderCountByDepartmentUser, 0)
	for _, user := range taskWorkOrderCount {
		if _, ok := departmentMap[user.DepartmentId]; !ok {
			departmentMap[user.DepartmentId] = true
			queryDepartmentIdList = append(queryDepartmentIdList, user.DepartmentId)
		}
		if _, ok := userMap[user.Executor]; !ok {
			userMap[user.Executor] = true
			userIdList = append(userIdList, user.Executor)
		}

		taskWorkOrderUserCountMap[fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Executor, user.ReportType)] = user

	}

	// 查询出部门信息
	var departmentService Department
	departmentList, err := departmentService.getDepartmentList(tenantId, ClickHouse.DB.Where("id in ?", queryDepartmentIdList))

	if err != nil {
		return
	}

	var userService User
	userList, err := userService.GetUserListWithDepartmentId(tenantId, ClickHouse.DB.Where("user.id in ? and tenant_user.department_id in ?", userIdList, queryDepartmentIdList))
	if err != nil {
		return
	}

	departmentDataRef := make(map[int64]*types.TaskSurveyRes, len(departmentList))
	for _, department := range departmentList {
		parsedDepartmentId := int64(department.Id)
		var tempResItem = types.TaskSurveyRes{
			Id:                           parsedDepartmentId,
			Name:                         department.Name,
			Type:                         "department",
			ParentId:                     0,
			NotAssignExecutorTask:        0,
			NotStartTask:                 0,
			InProgressTask:               0,
			WaitReviewTask:               0,
			DoneTask:                     0,
			OverdueTask:                  0,
			ArtRepairWorkOrder:           0,
			QualityRepairWorkOrder:       0,
			TechSupportRepairWorkOrder:   0,
			ToolQuestionRepairWorkOrder:  0,
			OtherQuestionRepairWorkOrder: 0,
			TotalRepairWorkOrder:         0,
			DeprecatedTask:               0,
		}

		if _, ok := departmentNotAssignTaskCountMap[parsedDepartmentId]; ok {
			tempResItem.NotAssignExecutorTask = departmentNotAssignTaskCountMap[parsedDepartmentId]
		}

		if _, ok := overdueTaskDepartmentCountMap[parsedDepartmentId]; ok {
			tempResItem.OverdueTask = overdueTaskDepartmentCountMap[parsedDepartmentId]
		}

		res = append(res, tempResItem)
	}

	for _, user := range userList {
		parsedUserId := int64(user.Id)
		var tempResItem = types.TaskSurveyRes{
			Id:                           parsedUserId,
			Name:                         user.Name,
			Type:                         "user",
			ParentId:                     user.DepartmentId,
			NotStartTask:                 0,
			InProgressTask:               0,
			WaitReviewTask:               0,
			DoneTask:                     0,
			OverdueTask:                  0,
			ArtRepairWorkOrder:           0,
			QualityRepairWorkOrder:       0,
			TechSupportRepairWorkOrder:   0,
			ToolQuestionRepairWorkOrder:  0,
			OtherQuestionRepairWorkOrder: 0,
			TotalRepairWorkOrder:         0,
			DeprecatedTask:               0,
		}
		parsedMapKey := fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "not_started")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.NotStartTask = taskGroupByDepartmentCountMap[parsedMapKey].Count
			delete(taskGroupByDepartmentCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "in_progress")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.InProgressTask = taskGroupByDepartmentCountMap[parsedMapKey].Count
			delete(taskGroupByDepartmentCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "wait_review")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.WaitReviewTask = taskGroupByDepartmentCountMap[parsedMapKey].Count
			delete(taskGroupByDepartmentCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "done")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.DoneTask = taskGroupByDepartmentCountMap[parsedMapKey].Count
			delete(taskGroupByDepartmentCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d", user.DepartmentId, user.Id)
		if _, ok := overdueTaskUserCountMap[parsedMapKey]; ok {
			tempResItem.OverdueTask = overdueTaskUserCountMap[parsedMapKey].Count
			delete(overdueTaskUserCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "艺术返修")
		if _, ok := taskWorkOrderUserCountMap[parsedMapKey]; ok {
			tempResItem.ArtRepairWorkOrder = taskWorkOrderUserCountMap[parsedMapKey].Count
			delete(taskWorkOrderUserCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "质量返修")
		if _, ok := taskWorkOrderUserCountMap[parsedMapKey]; ok {
			tempResItem.QualityRepairWorkOrder = taskWorkOrderUserCountMap[parsedMapKey].Count
			delete(taskWorkOrderUserCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "技术支撑")
		if _, ok := taskWorkOrderUserCountMap[parsedMapKey]; ok {
			tempResItem.TechSupportRepairWorkOrder = taskWorkOrderUserCountMap[parsedMapKey].Count
			delete(taskWorkOrderUserCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "工具问题")
		if _, ok := taskWorkOrderUserCountMap[parsedMapKey]; ok {
			tempResItem.ToolQuestionRepairWorkOrder = taskWorkOrderUserCountMap[parsedMapKey].Count
			delete(taskWorkOrderUserCountMap, parsedMapKey)
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", user.DepartmentId, user.Id, "其他问题")
		if _, ok := taskWorkOrderUserCountMap[parsedMapKey]; ok {
			tempResItem.OtherQuestionRepairWorkOrder = taskWorkOrderUserCountMap[parsedMapKey].Count
			delete(taskWorkOrderUserCountMap, parsedMapKey)
		}
		tempResItem.TotalRepairWorkOrder = tempResItem.ArtRepairWorkOrder +
			tempResItem.QualityRepairWorkOrder + tempResItem.TechSupportRepairWorkOrder +
			tempResItem.ToolQuestionRepairWorkOrder + tempResItem.OtherQuestionRepairWorkOrder
		parsedMapKey = fmt.Sprintf("%d-%d", user.DepartmentId, user.Id)
		if _, ok := deprecatedTaskCountMap[parsedMapKey]; ok {
			tempResItem.DeprecatedTask = deprecatedTaskCountMap[parsedMapKey].Count
			delete(deprecatedTaskCountMap, parsedMapKey)
		}
		res = append(res, tempResItem)
	}
	for i, d := range res {
		if d.Type == "department" {
			departmentDataRef[d.Id] = &res[i]
		}
	}

	for i, d := range taskGroupByDepartmentCountMap {
		if _, ok := departmentDataRef[d.DepartmentId]; !ok {
			continue
		}
		switch d.StatusText {
		case "not_started":
			departmentDataRef[d.DepartmentId].NotStartTask += d.Count
			break
		case "in_progress":
			departmentDataRef[d.DepartmentId].InProgressTask += d.Count
			break
		case "wait_review":
			departmentDataRef[d.DepartmentId].WaitReviewTask += d.Count
			break
		case "done":
			departmentDataRef[d.DepartmentId].DoneTask += d.Count
			break
		}
		delete(taskGroupByDepartmentCountMap, i)
	}

	for i, d := range overdueTaskUserCountMap {
		if _, ok := departmentDataRef[d.DepartmentId]; !ok {
			continue
		}
		departmentDataRef[d.DepartmentId].OverdueTask += d.Count
		delete(overdueTaskUserCountMap, i)
	}

	for i, d := range taskWorkOrderUserCountMap {
		if _, ok := departmentDataRef[d.DepartmentId]; !ok {
			continue
		}
		switch d.ReportType {
		case "艺术返修":
			departmentDataRef[d.DepartmentId].ArtRepairWorkOrder += d.Count
			break
		case "质量返修":
			departmentDataRef[d.DepartmentId].QualityRepairWorkOrder += d.Count
			break
		case "技术支撑":
			departmentDataRef[d.DepartmentId].TechSupportRepairWorkOrder += d.Count
			break
		case "工具问题":
			departmentDataRef[d.DepartmentId].ToolQuestionRepairWorkOrder += d.Count
			break
		case "其他问题":
			departmentDataRef[d.DepartmentId].OtherQuestionRepairWorkOrder += d.Count
			break
		}
		departmentDataRef[d.DepartmentId].TotalRepairWorkOrder += d.Count
		delete(taskWorkOrderUserCountMap, i)
	}

	for i, d := range deprecatedTaskCountMap {
		if _, ok := departmentDataRef[d.DepartmentId]; !ok {
			continue
		}
		departmentDataRef[d.DepartmentId].DeprecatedTask += d.Count
		delete(deprecatedTaskCountMap, i)
	}
	return
}

// TaskSurveyByStepCategory 任务概览 工单统计 通过分类
func (t *Task) TaskSurveyByStepCategory(tenantId int64, projectId, startTime, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdFilter, StepIdFilter []int64) (res []types.TaskSurveyRes, err error) {
	// 查询 未开始,进行中,待审核,已完成
	countBaseStepList, err := t.getCountStepIdList()
	countBaseStepIdList := make([]int64, len(countBaseStepList))
	for i, step := range countBaseStepList {
		countBaseStepIdList[i] = step.Id
	}
	var taskGroupByDepartmentRes []types.TaskCountGroupByStepCategoryTaskStatus
	query := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN status on status.id = simpleJSONExtractInt(task.json, 'task_status')").
		Where("task.tenant_id = ? and task.step_type = 'step' "+
			"and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 "+
			"and task.step_id in ? and task.department_id > 0 ", tenantId, countBaseStepIdList)
	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if len(stepCategoryIdFilter) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdFilter)
	}

	if len(StepIdFilter) > 0 {
		query.Where("task.step_id in ?", StepIdFilter)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}
	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = task.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}
	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	query.
		Select("task.step_id as step_id," +
			"task.step_category_id      as step_category_id," +
			"multiIf(status.id in (1, 2, 18), 'not_started'," +
			"status.id in (4, 6), 'in_progress'," +
			"status.id in (5, 9, 19), 'wait_review'," +
			"status.id in (7, 8), 'done', 'default') as status_text")

	err = ClickHouse.DB.Table("(?) as t", query).
		Select("step_id, step_category_id, status_text, count(*) as count").
		Group("step_id, step_category_id, status_text").
		Find(&taskGroupByDepartmentRes).Error

	if err != nil {
		return
	}

	var stepIdList = make([]int64, 0)
	var stepMap = make(map[int64]bool)
	var queryStepCategoryIdList = make([]int64, 0)
	var stepCategoryMap = make(map[int64]bool)
	taskGroupByDepartmentCountMap := make(map[string]int64, 0)
	for _, re := range taskGroupByDepartmentRes {
		taskGroupByDepartmentCountMap[fmt.Sprintf("%d-%d-%s", re.StepCategoryId, re.StepId, re.StatusText)] = re.Count
		if _, ok := stepMap[re.StepId]; !ok {
			stepMap[re.StepId] = true
			stepIdList = append(stepIdList, re.StepId)
		}

		if _, ok := stepCategoryMap[re.StepCategoryId]; !ok {
			stepCategoryMap[re.StepCategoryId] = true
			queryStepCategoryIdList = append(queryStepCategoryIdList, re.StepCategoryId)
		}
	}

	// 未分配任务统计
	departmentNotAssignTaskCount, err := t.getStepCategoryNotAssignTaskCount(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdFilter, StepIdFilter)
	var stepCategoryNotAssignTaskCountMap = make(map[string]int64, 0)
	for _, count := range departmentNotAssignTaskCount {
		tempKey := fmt.Sprintf("%d-%d", count.StepCategoryId, count.StepId)
		stepCategoryNotAssignTaskCountMap[tempKey] = count.Count
		if _, ok := stepCategoryMap[count.StepCategoryId]; !ok {
			stepCategoryMap[count.StepCategoryId] = true
			queryStepCategoryIdList = append(queryStepCategoryIdList, count.StepCategoryId)
		}
	}

	// 逾期任务统计
	overdueTaskCount, err := t.getStepCategoryOverdueTaskCount(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdFilter, StepIdFilter)
	overdueTaskCountMap := make(map[string]int64, 0)

	for _, count := range overdueTaskCount {
		if _, ok := stepCategoryMap[count.StepCategoryId]; !ok {
			stepCategoryMap[count.StepCategoryId] = true
			queryStepCategoryIdList = append(queryStepCategoryIdList, count.StepCategoryId)
		}

		if _, ok := stepMap[count.StepId]; !ok {
			stepMap[count.StepId] = true
			stepIdList = append(stepIdList, count.StepId)
		}

		overdueTaskCountMap[fmt.Sprintf("%d-%d", count.StepCategoryId, count.StepId)] = count.Count

	}

	// 废除任务统计
	deprecatedTaskCount, err := t.getStepCategoryDeprecatedTaskCount(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdFilter, StepIdFilter)
	if err != nil {
		return
	}
	deprecatedTaskCountMap := make(map[string]int64)
	for _, count := range deprecatedTaskCount {
		if _, ok := stepCategoryMap[count.StepCategoryId]; !ok {
			stepCategoryMap[count.StepCategoryId] = true
			queryStepCategoryIdList = append(queryStepCategoryIdList, count.StepCategoryId)
		}
		if _, ok := stepMap[count.StepId]; !ok {
			stepMap[count.StepId] = true
			stepIdList = append(stepIdList, count.StepId)
		}
		tempKey := fmt.Sprintf("%d-%d", count.StepCategoryId, count.StepId)
		deprecatedTaskCountMap[tempKey] = count.Count
	}

	// 工单统计
	taskWorkOrderCount, err := t.getTaskWorkOrderCountByStepCategory(tenantId, projectId, startTime, endTime, episodeList, departmentIdList, stepCategoryIdFilter, StepIdFilter)
	if err != nil {
		return
	}
	taskWorkOrderCountMap := make(map[string]int64, 0)
	for _, user := range taskWorkOrderCount {
		if _, ok := stepCategoryMap[user.StepCategoryId]; !ok {
			stepCategoryMap[user.StepCategoryId] = true
			queryStepCategoryIdList = append(queryStepCategoryIdList, user.StepCategoryId)
		}
		if _, ok := stepMap[user.StepId]; !ok {
			stepMap[user.StepId] = true
			stepIdList = append(stepIdList, user.StepId)
		}
		taskWorkOrderCountMap[fmt.Sprintf("%d-%d-%s", user.StepCategoryId, user.StepId, user.ReportType)] = user.Count
	}

	// 查询出工序分类信息
	var stepCategoryService StepCategory
	stepCategoryList, err := stepCategoryService.GetStepCategoryList(ClickHouse.DB.Where("id in ?", queryStepCategoryIdList))

	if err != nil {
		return
	}

	var stepService Step
	stepList, err := stepService.GetStepList(ClickHouse.DB.Where("id in ?", stepIdList))
	if err != nil {
		return
	}

	for _, stepCategory := range stepCategoryList {
		id := int64(stepCategory.Id)
		var tempResItem = types.TaskSurveyRes{
			Id:                           id,
			Name:                         stepCategory.Name,
			Type:                         "stepCategory",
			ParentId:                     0,
			NotAssignExecutorTask:        0,
			NotStartTask:                 0,
			InProgressTask:               0,
			WaitReviewTask:               0,
			DoneTask:                     0,
			OverdueTask:                  0,
			ArtRepairWorkOrder:           0,
			QualityRepairWorkOrder:       0,
			TechSupportRepairWorkOrder:   0,
			ToolQuestionRepairWorkOrder:  0,
			OtherQuestionRepairWorkOrder: 0,
			TotalRepairWorkOrder:         0,
			DeprecatedTask:               0,
		}
		res = append(res, tempResItem)
	}

	for _, step := range stepList {
		id := step.Id
		var tempResItem = types.TaskSurveyRes{
			Id:                           id,
			Name:                         step.Name,
			Type:                         "step",
			ParentId:                     step.StepCategoryId,
			NotStartTask:                 0,
			InProgressTask:               0,
			WaitReviewTask:               0,
			DoneTask:                     0,
			OverdueTask:                  0,
			ArtRepairWorkOrder:           0,
			QualityRepairWorkOrder:       0,
			TechSupportRepairWorkOrder:   0,
			ToolQuestionRepairWorkOrder:  0,
			OtherQuestionRepairWorkOrder: 0,
			TotalRepairWorkOrder:         0,
			DeprecatedTask:               0,
		}
		parsedMapKey := fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "not_started")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.NotStartTask = taskGroupByDepartmentCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "in_progress")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.InProgressTask = taskGroupByDepartmentCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "wait_review")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.WaitReviewTask = taskGroupByDepartmentCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "done")
		if _, ok := taskGroupByDepartmentCountMap[parsedMapKey]; ok {
			tempResItem.DoneTask = taskGroupByDepartmentCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d", step.StepCategoryId, step.Id)
		if _, ok := overdueTaskCountMap[parsedMapKey]; ok {
			tempResItem.OverdueTask = overdueTaskCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "艺术返修")
		if _, ok := taskWorkOrderCountMap[parsedMapKey]; ok {
			tempResItem.ArtRepairWorkOrder = taskWorkOrderCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "质量返修")
		if _, ok := taskWorkOrderCountMap[parsedMapKey]; ok {
			tempResItem.QualityRepairWorkOrder = taskWorkOrderCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "技术支撑")
		if _, ok := taskWorkOrderCountMap[parsedMapKey]; ok {
			tempResItem.TechSupportRepairWorkOrder = taskWorkOrderCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "工具问题")
		if _, ok := taskWorkOrderCountMap[parsedMapKey]; ok {
			tempResItem.ToolQuestionRepairWorkOrder = taskWorkOrderCountMap[parsedMapKey]
		}

		parsedMapKey = fmt.Sprintf("%d-%d-%s", step.StepCategoryId, step.Id, "其他问题")
		if _, ok := taskWorkOrderCountMap[parsedMapKey]; ok {
			tempResItem.OtherQuestionRepairWorkOrder = taskWorkOrderCountMap[parsedMapKey]
		}
		tempResItem.TotalRepairWorkOrder = tempResItem.ArtRepairWorkOrder +
			tempResItem.QualityRepairWorkOrder + tempResItem.TechSupportRepairWorkOrder +
			tempResItem.ToolQuestionRepairWorkOrder + tempResItem.OtherQuestionRepairWorkOrder
		parsedMapKey = fmt.Sprintf("%d-%d", step.StepCategoryId, step.Id)
		if _, ok := deprecatedTaskCountMap[parsedMapKey]; ok {
			tempResItem.DeprecatedTask = deprecatedTaskCountMap[parsedMapKey]
		}

		res = append(res, tempResItem)
	}

	return
}

// getDepartmentNotAssignTaskCount 查询部门未分配统计
func (t *Task) getDepartmentNotAssignTaskCount(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdList, stepIdList []int64) (res []types.DepartmentNotAssignCount, err error) {
	query := ClickHouse.DB.Model(&model.Task{}).Where("task.tenant_id = ? and task.step_type = 'step' and task.executor = 0 and task.department_id > 0 and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3", tenantId)
	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}

	if len(stepIdList) > 0 {
		query.Where("task.step_id in ?", stepIdList)
	}
	if len(stepCategoryIdList) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdList)
	}

	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = task.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	err = query.Group("task.department_id").Select("task.department_id as department_id,count(*) as count").Find(&res).Error

	return
}

// getStepCategoryNotAssignTaskCount 查询部门未分配统计
func (t *Task) getStepCategoryNotAssignTaskCount(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdFilter, StepIdFilter []int64) (res []types.StepCategoryStepNotAssignCount, err error) {
	query := ClickHouse.DB.Model(&model.Task{}).Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 and task.tenant_id = ? and task.step_type = 'step' and task.executor = 0 and task.department_id > 0", tenantId)
	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if len(stepCategoryIdFilter) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdFilter)
	}

	if len(StepIdFilter) > 0 {
		query.Where("task.step_id in ?", StepIdFilter)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}

	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = task.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	err = query.Group("task.step_category_id,task.step_id").Select("task.step_category_id as step_category_id, task.step_id as step_id, count(*) as count").Find(&res).Error

	return
}

// getDepartmentOverdueTaskCount 逾期任务统计
func (t *Task) getDepartmentOverdueTaskCount(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdList, stepIdList []int64) (res []types.DepartmentOverdueTaskCount, err error) {
	query := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ? and task.step_type = 'step' and task.department_id > 0", tenantId).
		Where("simpleJSONExtractInt(task.json, 'task_status') in (select id from status where code not in ? ) and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3", []string{"final", "closed"}).
		Where("task.plan_end_time < toUnixTimestamp(now())")

	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if len(stepIdList) > 0 {
		query.Where("task.step_id in ?", stepIdList)
	}
	if len(stepCategoryIdList) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdList)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}

	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = task.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	err = query.Group("task.department_id,task.executor").Select("task.department_id as department_id,task.executor as executor,count(*) as count").Find(&res).Error

	return
}

// getStepCategoryOverdueTaskCount 逾期任务统计
func (t *Task) getStepCategoryOverdueTaskCount(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdFilter, StepIdFilter []int64) (res []types.StepCategoryOverdueTaskCount, err error) {
	query := ClickHouse.DB.Model(&model.Task{}).
		Where("task.tenant_id = ? and task.step_type = 'step' and task.department_id > 0", tenantId).
		Where("simpleJSONExtractInt(task.json, 'task_status') in (select id from status where code not in ? ) and JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3", []string{"final", "closed"}).
		Where("task.plan_end_time < toUnixTimestamp(now())")

	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if len(stepCategoryIdFilter) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdFilter)
	}

	if len(StepIdFilter) > 0 {
		query.Where("task.step_id in ?", StepIdFilter)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}

	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = task.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	err = query.Group("task.step_category_id,task.step_id").Select("task.step_category_id as step_category_id, task.step_id as step_id, count(*) as count").Find(&res).Error

	return
}

// getDepartmentDeprecatedTaskCount 废除任务统计
func (t *Task) getDepartmentDeprecatedTaskCount(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdList, stepIdList []int64) (res []types.DepartmentDeprecatedTaskCount, err error) {
	query := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN entity on entity.id = task.entity_id").
		Where("task.tenant_id = ? and task.step_type = 'step'", tenantId).
		Where("JSONExtractString(task.json, 'task_deprecate_data') <> ''")

	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if len(stepIdList) > 0 {
		query.Where("task.step_id in ?", stepIdList)
	}
	if len(stepCategoryIdList) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdList)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}

	if len(episodeList) > 0 {
		query = query.Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	err = query.Group("task.department_id,task.executor").
		Select("task.department_id as department_id,task.executor as executor,count(*) as count").
		Find(&res).Error

	return
}

// getStepCategoryDeprecatedTaskCount 废除任务统计
func (t *Task) getStepCategoryDeprecatedTaskCount(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList, stepCategoryIdFilter, StepIdFilter []int64) (res []types.StepCategoryDeprecatedTaskCount, err error) {
	query := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN entity on entity.id = task.entity_id").
		Where("task.tenant_id = ? and task.step_type = 'step'", tenantId).
		Where("JSONExtractString(task.json, 'task_deprecate_data') <> ''")

	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if len(stepCategoryIdFilter) > 0 {
		query.Where("task.step_category_id in ?", stepCategoryIdFilter)
	}

	if len(StepIdFilter) > 0 {
		query.Where("task.step_id in ?", StepIdFilter)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("task.plan_end_time between ? and ?", startTime, endTime)
	}

	if len(episodeList) > 0 {
		query = query.Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("task.department_id in ?", departmentIdList)
	}

	err = query.Group("task.step_category_id,task.step_id").Select("task.step_category_id as step_category_id,task.step_id as step_id,count(*) as count").Find(&res).Error

	return
}

// getTaskWorkOrderCountByDepartmentUser 查询工单统计
func (t *Task) getTaskWorkOrderCountByDepartmentUser(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList []int64, stepCategoryIdList, stepIdList []int64) (res []types.TaskWorkOrderCountByDepartmentUser, err error) {

	query := ClickHouse.DB.Table("task_work_order as two").
		Joins("JOIN task t on t.id = two.task_id").
		Where("two.tenant_id = ?", tenantId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	if projectId > 0 {
		query.Where("t.project_id = ?", projectId)
	}

	if len(stepIdList) > 0 {
		query.Where("t.step_id in ?", stepIdList)
	}
	if len(stepCategoryIdList) > 0 {
		query.Where("t.step_category_id in ?", stepCategoryIdList)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("two.created between ? and ?", startTime, endTime)
	}

	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = t.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("t.department_id in ?", departmentIdList)
	}

	err = query.Group("t.department_id,two.created_by,two.report_type").
		Select("t.department_id as department_id,two.created_by as executor,two.report_type as report_type,count(*) as count").
		Find(&res).Error

	return
}

// getTaskWorkOrderCountByStepCategory 查询工单统计
func (t *Task) getTaskWorkOrderCountByStepCategory(tenantId int64, projectId int64, startTime int64, endTime int64, episodeList []types.EpisodeShowingsItem, departmentIdList, stepCategoryIdFilter, StepIdFilter []int64) (res []types.TaskWorkOrderCountByStepCategory, err error) {

	query := ClickHouse.DB.Table("task_work_order as two").
		Joins("JOIN task t on t.id = two.task_id").
		Where("two.tenant_id = ?", tenantId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	if projectId > 0 {
		query.Where("t.project_id = ?", projectId)
	}

	if len(stepCategoryIdFilter) > 0 {
		query.Where("t.step_category_id in ?", stepCategoryIdFilter)
	}

	if len(StepIdFilter) > 0 {
		query.Where("t.step_id in ?", StepIdFilter)
	}

	if startTime > 0 && endTime > 0 {
		query.Where("two.created between ? and ?", startTime, endTime)
	}

	if len(episodeList) > 0 {
		query = query.Joins("JOIN entity on entity.id = t.entity_id").Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}

	if len(departmentIdList) > 0 {
		query = query.Where("t.department_id in ?", departmentIdList)
	}

	err = query.Group("t.step_category_id,t.step_id,two.report_type").
		Select("t.step_category_id as step_category_id,t.step_id as step_id,two.report_type as report_type,count(*) as count").
		Find(&res).Error

	return
}

// TaskSurveyDetailList 生产统计报表详情
func (t *Task) TaskSurveyDetailList(tenantId int64, id, parentId int64, columnKey, dataType string, page []int, projectId int64, startTime, endTime int64, episodeList []types.EpisodeShowingsItem) (res []types.TaskSurveyDetailListRes, err error) {

	type resBaseDataStruct struct {
		TaskId             int64
		EntityModuleId     int64
		EntityJson         datatypes.JSON
		EntityName         string
		InitialEpisodeCode string
		ShowingsNumber     string
		StepId             int64
		StepCategoryId     int64
		ExecutorTenantId   int64
		ProjectId          int64
		DepartmentId       int64
		Executor           int64
		AssigneeProducer   int64
	}

	taskColumn := strings.HasSuffix(columnKey, "task")

	var resBaseData []resBaseDataStruct
	query := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN entity on entity.id = task.entity_id").
		Where("task.tenant_id = ? and task.step_type = 'step'", tenantId)
	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}

	if len(episodeList) > 0 {
		query = query.Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}
	if taskColumn {
		if startTime > 0 && endTime > 0 {
			query.Where("task.plan_end_time between ? and ?", startTime, endTime)
		}
	}

	var dataWhere interface{}
	var workOrderWhere interface{}
	if dataType == "department" {
		dataWhere = ClickHouse.DB.Where("task.department_id = ?", id)
		workOrderWhere = dataWhere
	} else if dataType == "user" {
		dataWhere = ClickHouse.DB.Where("task.executor = ? and task.department_id = ?", id, parentId)
		workOrderWhere = ClickHouse.DB.Where("two.created_by = ? and task.department_id = ?", id, parentId)
	} else if dataType == "step" {
		dataWhere = ClickHouse.DB.Where("task.step_id = ?", id)
		workOrderWhere = dataWhere
	} else if dataType == "stepCategory" {
		dataWhere = ClickHouse.DB.Where("task.step_category_id = ?", id)
		workOrderWhere = dataWhere
	} else {
		log.Println(dataType, "dataType not found")
	}

	if columnKey == "not_assign_executor_task" {
		query.Where(dataWhere).Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 and task.department_id > 0 and task.executor = 0")
	} else if columnKey == "not_start_task" {
		query.Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 " +
			"and task.department_id > 0 and simpleJSONExtractInt(task.json, 'task_status') in (1, 2, 18)").Where(dataWhere)
	} else if columnKey == "in_progress_task" {
		query.Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 " +
			"and task.department_id > 0 and simpleJSONExtractInt(task.json, 'task_status') in (4, 6)").Where(dataWhere)
	} else if columnKey == "wait_review_task" {
		query.Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 " +
			"and task.department_id > 0 and simpleJSONExtractInt(task.json, 'task_status') in (5, 9, 19)").Where(dataWhere)
	} else if columnKey == "done_task" {
		query.Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 " +
			"and task.department_id > 0 and simpleJSONExtractInt(task.json, 'task_status') in (7, 8)").Where(dataWhere)
	} else if columnKey == "overdue_task" {
		query.Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3 "+
			"and task.department_id > 0").
			Where("simpleJSONExtractInt(task.json, 'task_status') in (select id from status where code not in ? )", []string{"final", "closed"}).
			Where("task.plan_end_time < toUnixTimestamp(now())").Where(dataWhere)
	} else if columnKey == "art_repair_work_order" {
		dataWhere = workOrderWhere
		taskIdList, erre := t.getTaskWorkOrderTaskIdWhere(tenantId, projectId, startTime, endTime, episodeList,
			ClickHouse.DB.Where(dataWhere).
				Where("simpleJSONExtractInt(task.json, 'task_status') <> (select id from status where code = ? ) "+
					"and two.report_type = ?", "closed", "艺术返修"))
		if erre != nil || len(taskIdList) == 0 {
			err = erre
			return
		}
		query.Where("task.id in ?", taskIdList)

	} else if columnKey == "quality_repair_work_order" {
		dataWhere = workOrderWhere
		taskIdList, erre := t.getTaskWorkOrderTaskIdWhere(tenantId, projectId, startTime, endTime, episodeList,
			ClickHouse.DB.Where(dataWhere).
				Where("simpleJSONExtractInt(task.json, 'task_status') <> (select id from status where code = ? ) "+
					"and two.report_type = ?", "closed", "质量返修"))
		if erre != nil || len(taskIdList) == 0 {
			err = erre
			return
		}
		query.Where("task.id in ?", taskIdList)
	} else if columnKey == "tech_support_repair_work_order" {
		dataWhere = workOrderWhere
		taskIdList, erre := t.getTaskWorkOrderTaskIdWhere(tenantId, projectId, startTime, endTime, episodeList,
			ClickHouse.DB.Where(dataWhere).
				Where("simpleJSONExtractInt(task.json, 'task_status') <> (select id from status where code = ? ) "+
					"and two.report_type = ?", "closed", "技术支撑"))
		if erre != nil || len(taskIdList) == 0 {
			err = erre
			return
		}
		query.Where("task.id in ?", taskIdList)

	} else if columnKey == "tool_question_repair_work_order" {
		dataWhere = workOrderWhere
		taskIdList, erre := t.getTaskWorkOrderTaskIdWhere(tenantId, projectId, startTime, endTime, episodeList,
			ClickHouse.DB.Where(dataWhere).
				Where("simpleJSONExtractInt(task.json, 'task_status') <> (select id from status where code = ? ) "+
					"and two.report_type = ?", "closed", "工具问题"))
		if erre != nil || len(taskIdList) == 0 {
			err = erre
			return
		}
		query.Where("task.id in ?", taskIdList)

	} else if columnKey == "other_question_repair_work_order" {
		dataWhere = workOrderWhere
		taskIdList, erre := t.getTaskWorkOrderTaskIdWhere(tenantId, projectId, startTime, endTime, episodeList,
			ClickHouse.DB.Where(dataWhere).
				Where("simpleJSONExtractInt(task.json, 'task_status') <> (select id from status where code = ? ) "+
					"and two.report_type = ?", "closed", "其他问题"))
		if erre != nil || len(taskIdList) == 0 {
			err = erre
			return
		}
		query.Where("task.id in ?", taskIdList)
	} else if columnKey == "total_repair_work_order" {
		dataWhere = workOrderWhere
		taskIdList, erre := t.getTaskWorkOrderTaskIdWhere(tenantId, projectId, startTime, endTime, episodeList,
			ClickHouse.DB.Where(dataWhere).
				Where("simpleJSONExtractInt(task.json, 'task_status') <> (select id from status where code = ? )", "closed"))
		if erre != nil || len(taskIdList) == 0 {
			err = erre
			return
		}
		query.Where("task.id in ?", taskIdList)
	} else if columnKey == "deprecated_task" {
		if startTime > 0 && endTime > 0 {
			query.Where("task.plan_end_time between ? and ?", startTime, endTime)
		}
		query.Where("JSONExtractString(task.json, 'task_deprecate_data') <> ''").Where(dataWhere)
	}
	err = query.
		Select("entity.json as entity_json,entity.module_id as entity_module_id,entity.initial_episode_code as initial_episode_code,entity.showings_number as showings_number,task.id as task_id,entity.name as entity_name,task.step_id as step_id," +
			"task.step_category_id as step_category_id,task.assignee_producer as assignee_producer,task.executor as executor,task.executor_tenant_id as executor_tenant_id,task.project_id as project_id,task.department_id as department_id").
		Offset((page[0] - 1) * page[1]).Limit(page[1]).Find(&resBaseData).Error

	if err != nil {
		return
	}

	tenantIdList := make([]int64, 0)
	tenantIdMap := make(map[int64]bool)
	projectIdList := make([]int64, 0)
	projectIdMap := make(map[int64]bool)
	stepIdList := make([]int64, 0)
	stepIdMap := make(map[int64]bool)
	stepCategoryIdList := make([]int64, 0)
	stepCategoryIdMap := make(map[int64]bool)
	departmentIdList := make([]int64, 0)
	departmentIdMap := make(map[int64]bool)
	userIdMap := make(map[int64]bool, 0)
	userIdList := make([]int64, 0)

	var assetModules []model.Module
	err = ClickHouse.DB.Model(&model.Module{}).Where("code in ?", []string{"design", "asset", "level"}).Find(&assetModules).Error
	if err != nil {
		return
	}
	assetModuleIdMap := make(map[int64]*model.Module)
	for i, module := range assetModules {
		assetModuleIdMap[module.Id] = &assetModules[i]
	}

	for _, datum := range resBaseData {
		if _, ok := tenantIdMap[datum.ExecutorTenantId]; !ok {
			tenantIdMap[datum.ExecutorTenantId] = true
			tenantIdList = append(tenantIdList, datum.ExecutorTenantId)
		}
		if _, ok := projectIdMap[datum.ProjectId]; !ok {
			projectIdMap[datum.ProjectId] = true
			projectIdList = append(projectIdList, datum.ProjectId)
		}
		if _, ok := stepIdMap[datum.StepId]; !ok {
			stepIdMap[datum.StepId] = true
			stepIdList = append(stepIdList, datum.StepId)
		}
		if _, ok := stepCategoryIdMap[datum.StepCategoryId]; !ok {
			stepCategoryIdMap[datum.StepCategoryId] = true
			stepCategoryIdList = append(stepCategoryIdList, datum.StepCategoryId)
		}
		if _, ok := departmentIdMap[datum.DepartmentId]; !ok {
			departmentIdMap[datum.DepartmentId] = true
			departmentIdList = append(departmentIdList, datum.DepartmentId)
		}
		if datum.Executor > 0 {
			if _, ok := userIdMap[datum.Executor]; !ok {
				userIdMap[datum.Executor] = true
				userIdList = append(userIdList, datum.Executor)
			}
		}
		if datum.AssigneeProducer > 0 {
			if _, ok := userIdMap[datum.AssigneeProducer]; !ok {
				userIdMap[datum.AssigneeProducer] = true
				userIdList = append(userIdList, datum.AssigneeProducer)
			}
		}

	}
	var departmentService Department
	departmentList, err := departmentService.getDepartmentList(tenantId, ClickHouse.DB.Where("id in ?", departmentIdList))
	if err != nil {
		return
	}
	var departmentMap = make(map[int64]*model.Department)
	for i, department := range departmentList {
		departmentMap[int64(department.Id)] = &departmentList[i]
	}
	var stepCategoryService StepCategory
	stepCategoryList, err := stepCategoryService.GetStepCategoryList(ClickHouse.DB.Where("id in ?", stepCategoryIdList))
	if err != nil {
		return
	}
	var stepCategoryMap = make(map[int64]*model.StepCategory)
	for i, stepCategory := range stepCategoryList {
		stepCategoryMap[int64(stepCategory.Id)] = &stepCategoryList[i]
	}
	var stepService Step
	stepList, err := stepService.GetStepList(ClickHouse.DB.Where("id in ?", stepIdList))
	if err != nil {
		return
	}
	var stepMap = make(map[int64]*model.Step)
	for i, step := range stepList {
		stepMap[int64(step.Id)] = &stepList[i]
	}
	var projectService Project
	projectList, err := projectService.GetList(ClickHouse.DB.Where("id in ?", projectIdList))
	if err != nil {
		return
	}
	var projectMap = make(map[int64]*model.Project)
	for i, project := range projectList {
		projectMap[int64(project.Id)] = &projectList[i]
	}
	var tenantService Tenant
	tenantList, err := tenantService.GetList(ClickHouse.DB.Where("id in ?", tenantIdList))
	if err != nil {
		return
	}
	var tenantMap = make(map[int64]*model.Tenant)
	for i, tenant := range tenantList {
		tenantMap[int64(tenant.Id)] = &tenantList[i]
	}

	var userService User
	userList, err := userService.GetUserList(ClickHouse.DB.Where("id in ?", userIdList))
	if err != nil {
		return
	}
	var userMap = make(map[int64]*model.User)
	for i, user := range userList {
		userMap[int64(user.Id)] = &userList[i]
	}
	for _, datum := range resBaseData {

		var tempResItem = types.TaskSurveyDetailListRes{
			TaskId:           datum.TaskId,
			TaskName:         fmt.Sprintf("%s/%s/%s", datum.InitialEpisodeCode, datum.ShowingsNumber, datum.EntityName),
			AssetGrade:       "",
			AssetType:        "",
			StepCategoryName: "",
			StepName:         "",
			CompanyName:      "",
			DepartmentName:   "",
			PersonInCharge:   "",
			Executor:         "",
		}

		if _, ok := tenantMap[datum.ExecutorTenantId]; ok {
			tempResItem.CompanyName = tenantMap[datum.ExecutorTenantId].Name
		}
		if _, ok := projectMap[datum.ProjectId]; ok {
			tempResItem.ProjectName = projectMap[datum.ProjectId].Name
		}
		if _, ok := stepMap[datum.StepId]; ok {
			tempResItem.StepName = stepMap[datum.StepId].Name
		}
		if _, ok := stepCategoryMap[datum.StepCategoryId]; ok {
			tempResItem.StepCategoryName = stepCategoryMap[datum.StepCategoryId].Name
		}
		if _, ok := departmentMap[datum.DepartmentId]; ok {
			tempResItem.DepartmentName = departmentMap[datum.DepartmentId].Name
		}
		if _, ok := userMap[datum.Executor]; ok {
			tempResItem.Executor = userMap[datum.Executor].Name
		}
		if _, ok := userMap[datum.AssigneeProducer]; ok {
			tempResItem.PersonInCharge = userMap[datum.AssigneeProducer].Name
		}

		if _, ok := assetModuleIdMap[datum.EntityModuleId]; ok {
			switch assetModuleIdMap[datum.EntityModuleId].Code {
			case "design":
				var designJson model.DesignJson
				_ = json.Unmarshal(datum.EntityJson, &designJson)
				tempResItem.AssetGrade = designJson.DesignGrade
				tempResItem.AssetType = designJson.DesignCategory
				break
			case "asset":
				var assetJson model.AssetJson
				_ = json.Unmarshal(datum.EntityJson, &assetJson)
				tempResItem.AssetGrade = assetJson.AssetGrade
				tempResItem.AssetType = assetJson.AssetCategory
				break
			case "level":
				var levelJson model.LevelJson
				_ = json.Unmarshal(datum.EntityJson, &levelJson)
				tempResItem.AssetGrade = levelJson.LevelGrade
				tempResItem.AssetType = levelJson.LevelCategory

			}
		}

		res = append(res, tempResItem)
	}

	return
}

func (t *Task) getTaskWorkOrderTaskIdWhere(tenantId int64, projectId, startTime, endTime int64, episodeList []types.EpisodeShowingsItem, where interface{}) (res []int64, err error) {

	query := ClickHouse.DB.Model(&model.Task{}).
		Joins("JOIN entity on entity.id = task.entity_id").
		Where("task.tenant_id = ? and task.step_type = 'step'", tenantId).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3")

	if projectId > 0 {
		query.Where("task.project_id = ?", projectId)
	}
	// 针对工单和任务 区分字段
	if startTime > 0 && endTime > 0 {
		query.Where("two.created between ? and ?", startTime, endTime)
	}
	if len(episodeList) > 0 {
		query = query.Where(BuildEpisodeShowingsNumberWhere(episodeList))
	}
	err = query.Joins("JOIN task_work_order two on two.task_id = task.id").
		Where(where).Group("task.id").Pluck("task.id", &res).Error

	return
}

// getRepairTaskCount 获取返修状态任务统计
func (t *Task) getRepairTaskCount(req types.TodoTaskOverviewReq, userId, tenantId int64, rc chan types.TodoTaskOverviewResItemAsync, code string) (res types.TodoTaskOverviewResItemAsync) {
	var err error
	defer func() {
		res.CountItem.Code = code
		res.Err = err
		rc <- res
	}()

	saasUserId, errOccurred := GetSaasUserIdByUnionId(userId)
	if errOccurred != nil {
		err = errOccurred
		res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
		return
	}

	db := ClickHouse.DB.Model(&model.Task{})

	status, err := model.GetStatusByCode("remake")
	if status == (model.Status{}) {
		err = errors.New("status is empty")
	}

	if err != nil {
		return
	}

	if len(req.ProjectIdList) > 0 {
		db = db.Where("task.project_id in ?", req.ProjectIdList)
	} else {
		imUserProjectIds, errs := GetUserProjectIdListByRoleCode(saasUserId, tenantId, []string{}, []string{"in_progress"})
		if errs != nil {
			err = errs
			return
		}
		if len(imUserProjectIds) == 0 {
			res.CountItem.CountData = make([]types.TodoTaskOverviewResItemCountData, 0)
			return
		}
		db = db.Where("task.project_id in ?", imUserProjectIds)
	}

	//计划完成时间筛选
	if req.StartTime > 0 {
		db = db.Where("task.plan_end_time >= ?", req.StartTime)
	}
	if req.EndTime > 0 {
		db = db.Where("task.plan_end_time <= ?", req.EndTime)
	}

	//工单状态范围限制
	err = db.
		Where("task.step_type = 'step' and task.executor_tenant_id = ?", tenantId).
		Where("simpleJSONExtractUInt(task.json, 'task_status') = ?", status.Id).
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Select("count(*) as number,task.project_id as project_id").
		Group("project_id").
		Find(&res.CountItem.CountData).
		Error

	return
}

// GetTaskCompletionTotalMap
func (t *Task) GetTaskCompletionTotalMap(params types.GetTaskCompletionReq) (totalMap map[string]int64) {

	var statusService Status // 所有状态
	totalMap = make(map[string]int64, 0)
	statusList, statusErr := statusService.GetStatus()
	if statusErr != nil {
		return
	}

	var statusCodeMap = make(map[string]*model.Status)
	var notStatusIds = make([]int64, 0) // 排除待发布,已完成,已取消
	notCode := map[string]bool{
		"ready_to_publish": true,
		"final":            true,
		"closed":           true,
	}

	for i, status := range statusList {
		statusCodeMap[status.Code] = &statusList[i]
		if _, ok := notCode[status.Code]; !ok {
			notStatusIds = append(notStatusIds, status.Id)
		}
	}
	var TaskWeekMeetService TaskWeekMeeting
	params.NotStatusIds = notStatusIds
	// 逾期交付
	TotalOverdueDeliverWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("TotalOverdueDeliver", params, statusCodeMap))
	TotalOverdueDeliver, _ := TaskWeekMeetService.GetTaskCompletionCount(TotalOverdueDeliverWhere, params)
	totalMap["TotalOverdueDeliver"] = TotalOverdueDeliver
	// 仍逾期
	StillOverdueWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("StillOverdue", params, statusCodeMap))
	StillOverdue, _ := TaskWeekMeetService.GetTaskCompletionCount(StillOverdueWhere, params)
	totalMap["StillOverdue"] = StillOverdue
	// 未完成
	undoneWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("undone", params, statusCodeMap))
	undone, _ := TaskWeekMeetService.GetTaskCompletionCount(undoneWhere, params)
	totalMap["undone"] = undone
	// 已完成
	finalWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("final", params, statusCodeMap))
	final, _ := TaskWeekMeetService.GetTaskCompletionCount(finalWhere, params)
	totalMap["final"] = final
	// 已交付
	paidWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("paid", params, statusCodeMap))
	paid, _ := TaskWeekMeetService.GetTaskCompletionCount(paidWhere, params)
	totalMap["paid"] = paid
	// 已逾期
	PastDueWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("PastDue", params, statusCodeMap))
	PastDue, _ := TaskWeekMeetService.GetTaskCompletionCount(PastDueWhere, params)
	totalMap["PastDue"] = PastDue
	// 超额-已完成
	ExcessFinalWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("ExcessFinal", params, statusCodeMap))
	ExcessFinal, _ := TaskWeekMeetService.GetTaskCompletionCount(ExcessFinalWhere, params)
	totalMap["ExcessFinal"] = ExcessFinal
	// 超额-已交付
	ExcessPaidWhere := ClickHouse.DB.Where(TaskWeekMeetService.TaskCompletionWhere("ExcessPaid", params, statusCodeMap))
	ExcessPaid, _ := TaskWeekMeetService.GetTaskCompletionCount(ExcessPaidWhere, params)
	totalMap["ExcessPaid"] = ExcessPaid
	return
}

// TaskCompletion 任务完成情况
func (t *Task) TaskCompletion(req types.TaskCompletionReq, userId int64, tenantId int64) (res types.WeekMeetingTaskFinishItem, err error) {

	var StepCategoryIds []uint64 // 工序分类信息
	var stepCategoryService StepCategory
	var Excess []types.WeekMeetingItem
	var ThisWeek []types.WeekMeetingItem
	var BeforeTotal []types.WeekMeetingItem
	var TaskCompletionParam types.GetTaskCompletionReq

	// 查询工序分类信息
	stepCategoryList, stepCategoryErr := stepCategoryService.GetStepCategoryList(ClickHouse.DB.Where("code in (?)", req.StepCategoryCode))
	if stepCategoryErr != nil {
		err = stepCategoryErr
		return
	}
	for _, v := range stepCategoryList {
		StepCategoryIds = append(StepCategoryIds, v.Id)
	}

	TaskCompletionParam.EndTime = req.EndTime
	TaskCompletionParam.StartTime = req.StartTime
	TaskCompletionParam.ProjectIdList = req.ProjectIdList
	TaskCompletionParam.StepCategoryIds = StepCategoryIds
	TaskCompletionParam.StepCategoryCode = req.StepCategoryCode
	TaskCompletionParam.TenantId = tenantId

	totalMap := t.GetTaskCompletionTotalMap(TaskCompletionParam) // 计算统计数据

	BeforeTotal = append(BeforeTotal, types.WeekMeetingItem{
		Name:            "逾期交付",
		Value:           totalMap["TotalOverdueDeliver"],
		StatisticType:   "TotalOverdueDeliver",
		StepCategoryIds: StepCategoryIds,
	})
	BeforeTotal = append(BeforeTotal, types.WeekMeetingItem{
		Name:            "仍逾期",
		Value:           totalMap["StillOverdue"],
		StatisticType:   "StillOverdue",
		StepCategoryIds: StepCategoryIds,
	})
	BeforeTotal = append(BeforeTotal, types.WeekMeetingItem{
		Name:            "总逾期",
		StatisticType:   "TotalOverdue",
		Value:           totalMap["TotalOverdueDeliver"] + totalMap["StillOverdue"],
		StepCategoryIds: StepCategoryIds,
	})

	ThisWeek = append(ThisWeek, types.WeekMeetingItem{
		Name:            "已完成",
		Value:           totalMap["final"],
		StatisticType:   "final",
		StepCategoryIds: StepCategoryIds,
	})
	ThisWeek = append(ThisWeek, types.WeekMeetingItem{
		Name:            "已交付",
		Value:           totalMap["paid"],
		StatisticType:   "paid",
		StepCategoryIds: StepCategoryIds,
	})
	ThisWeek = append(ThisWeek, types.WeekMeetingItem{
		Name:            "未完成",
		Value:           totalMap["undone"],
		StatisticType:   "undone",
		StepCategoryIds: StepCategoryIds,
	})
	ThisWeek = append(ThisWeek, types.WeekMeetingItem{
		Name:            "已逾期",
		Value:           totalMap["PastDue"],
		StatisticType:   "PastDue",
		StepCategoryIds: StepCategoryIds,
	})
	ThisWeek = append(ThisWeek, types.WeekMeetingItem{
		Name:            "应交付",
		StatisticType:   "ShouldBeDelivered",
		Value:           totalMap["final"] + totalMap["paid"] + totalMap["undone"] + totalMap["PastDue"],
		StepCategoryIds: StepCategoryIds,
	})

	Excess = append(Excess, types.WeekMeetingItem{
		Name:            "已完成",
		Value:           totalMap["ExcessFinal"],
		StatisticType:   "ExcessFinal",
		StepCategoryIds: StepCategoryIds,
	})
	Excess = append(Excess, types.WeekMeetingItem{
		Name:            "已交付",
		Value:           totalMap["ExcessPaid"],
		StatisticType:   "ExcessPaid",
		StepCategoryIds: StepCategoryIds,
	})
	Excess = append(Excess, types.WeekMeetingItem{
		Name:            "超额",
		StatisticType:   "Excess",
		Value:           totalMap["ExcessFinal"] + totalMap["ExcessPaid"],
		StepCategoryIds: StepCategoryIds,
	})

	res = types.WeekMeetingTaskFinishItem{
		BeforeTotal: BeforeTotal,
		ThisWeek:    ThisWeek,
		Excess:      Excess,
	}
	return
}

// GetWorkOrderSituation
func (t *Task) GetWorkOrderSituation(param types.GetWorkOrderSituationReq) (resBaseData []resWorkOrderSituationStruct, err error) {

	// 角色模型,道具模型,场景模型
	var modelMap = make(map[string]bool, 0)
	var modelTag = make([]string, 0)
	for _, v := range param.StepCategoryCode {
		modelMap[v] = true
	}
	db := ClickHouse.DB.Model(&model.TaskWorkOrder{})
	db = db.
		Joins("JOIN task on task.id = task_work_order.task_id").
		Joins("JOIN entity on entity.id = task.entity_id").
		Where("task_work_order.tenant_id = ?", param.TenantId).
		Where("task_work_order.type = 'task_making'").
		Where("JSONLength(JSONExtractString(task.json, 'task_deprecate_data')) = 0 and simpleJSONExtractInt(task.json,'task_status') <> 3").
		Select("task.project_id as ProjectId,task.id as TaskId,task_work_order.status as WorkOrderStatus,task_work_order.report_type as WorkOrderReportType")

	// 创建时间筛选
	if param.EndTime > 0 {
		db.Where("task_work_order.created  BETWEEN ? AND ?", param.StartTime, param.EndTime)
	}
	if len(param.ProjectIdList) > 0 {
		db.Where("task.project_id IN (?)", param.ProjectIdList)
	}
	if len(param.StepCategoryIds) > 0 && param.WorkOrderType == "step_category" {
		db.Where("task.step_category_id IN (?)", param.StepCategoryIds)
	}
	if len(param.StepCategoryIds) > 0 && param.WorkOrderType == "relate_step" {
		db.Where("task_work_order.relate_step_category_id IN (?)", param.StepCategoryIds)
	}
	// 如果包含角色模型
	if _, ok := modelMap["role_model"]; ok {
		modelTag = append(modelTag, "角色")
	}
	// 如果包含道具模型
	if _, ok := modelMap["props_model"]; ok {
		modelTag = append(modelTag, "道具")
	}
	// 如果包含场景模型
	if _, ok := modelMap["scenes_model"]; ok {
		modelTag = append(modelTag, "场景")
	}
	if len(modelTag) > 0 && modelMap["model"] {
		db.Where("JSONExtractString(entity.json, 'asset_category') IN (?)", modelTag)
	}
	err = db.
		Find(&resBaseData).
		Error
	return
}

// WorkOrderSituation 工单情况
func (t *Task) WorkOrderSituation(req types.WorkOrderTaskCompletionReq, tenantId int64) (res types.WeekWorkOrderInfo, err error) {

	var stepCategoryService StepCategory
	var workOrderSituationParam types.GetWorkOrderSituationReq
	var stepCategoryIds []uint64 // 工序分类信息

	stepCategoryList, stepCategoryErr := stepCategoryService.GetStepCategoryList(ClickHouse.DB.Where("code in (?)", req.StepCategoryCode))
	if stepCategoryErr != nil {
		err = stepCategoryErr
		return
	}
	for _, v := range stepCategoryList {
		stepCategoryIds = append(stepCategoryIds, v.Id)
	}

	workOrderSituationParam.EndTime = req.EndTime                   // 结束时间
	workOrderSituationParam.TenantId = tenantId                     // 租户id
	workOrderSituationParam.StartTime = req.StartTime               // 开始时间
	workOrderSituationParam.ProjectIdList = req.ProjectIdList       // 项目id
	workOrderSituationParam.WorkOrderType = req.WorkOrderType       // 工单类型
	workOrderSituationParam.StepCategoryIds = stepCategoryIds       // 工序分类id
	workOrderSituationParam.StepCategoryCode = req.StepCategoryCode // 工序分类Code

	workOrderSituationData, workOrderSituationDataErr := t.GetWorkOrderSituation(workOrderSituationParam)
	if workOrderSituationDataErr != nil {
		err = workOrderSituationDataErr
		return
	}

	var SeriesItem = make(map[string]uint64, 0)         // 已解决
	var SeriesItemUnsolved = make(map[string]uint64, 0) // 未解决
	for _, v := range workOrderSituationData {
		if v.WorkOrderStatus == model.TaskWorkOrderProcessed {
			SeriesItem[v.WorkOrderReportType]++
		} else {
			SeriesItemUnsolved[v.WorkOrderReportType]++
		}
	}

	// 组织数据罗盘数据
	var Series []types.SeriesItem
	Series = append(Series, types.SeriesItem{
		Name:            "已解决",
		StepCategoryIds: stepCategoryIds,
		Data:            []uint64{SeriesItem["艺术返修"], SeriesItem["质量返修"], SeriesItem["技术支撑"], SeriesItem["工具问题"], SeriesItem["其他问题"]},
	})
	Series = append(Series, types.SeriesItem{
		Name:            "未解决",
		StepCategoryIds: stepCategoryIds,
		Data:            []uint64{SeriesItemUnsolved["艺术返修"], SeriesItemUnsolved["质量返修"], SeriesItemUnsolved["技术支撑"], SeriesItemUnsolved["工具问题"], SeriesItemUnsolved["其他问题"]},
	})

	res = types.WeekWorkOrderInfo{
		Series: Series,
		Data:   []string{"艺术返修", "质量返修", "技术支撑", "工具问题", "其他问题"},
	}
	return
}

// GetShouldBeDeliveredData 获取本周应交付
func (t *Task) GetShouldBeDeliveredData(param types.GetTaskCompletionReq, statusCodeMap map[string]*model.Status) (res []int64, err error) {

	var TaskWeekMeetingService TaskWeekMeeting

	// 已完成，已交付
	var completedAndDeliveredRes []resShouldBeDeliveredDataStruct
	db := ClickHouse.DB.Model(&model.Task{})
	db = db.
		Joins("LEFT JOIN entity on entity.id = task.entity_id").
		Where(TaskWeekMeetingService.TaskCompletionWhere("finalPaid", param, statusCodeMap)).
		Select("task.id as Id")
	err = db.
		Find(&completedAndDeliveredRes).
		Error

	// 未完成
	var undoneRes []resShouldBeDeliveredDataStruct
	undoneDb := ClickHouse.DB.Model(&model.Task{})
	undoneDb = undoneDb.
		Joins("LEFT JOIN entity on entity.id = task.entity_id").
		Where(TaskWeekMeetingService.TaskCompletionWhere("undone", param, statusCodeMap))
	undoneDb.Select("task.id as Id")
	err = undoneDb.
		Find(&undoneRes).
		Error

	// 已逾期
	var pastDueRes []resShouldBeDeliveredDataStruct
	pastDueDb := ClickHouse.DB.Model(&model.Task{})
	pastDueDb = pastDueDb.
		Joins("LEFT JOIN entity on entity.id = task.entity_id").
		Where(TaskWeekMeetingService.TaskCompletionWhere("PastDue", param, statusCodeMap))
	pastDueDb.Select("task.id as Id")
	err = pastDueDb.
		Find(&pastDueRes).
		Error

	for _, v := range completedAndDeliveredRes {
		res = append(res, v.Id)
	}
	for _, v := range undoneRes {
		res = append(res, v.Id)
	}
	for _, v := range pastDueRes {
		res = append(res, v.Id)
	}
	return
}

// GetTaskCompletedData
func (t *Task) GetTaskCompletedData(param types.GetTaskCompletionReq, statusCodeMap map[string]*model.Status) (res []TaskCompletedDataStruct, err error) {

	var TaskWeekMeetingService TaskWeekMeeting
	db := ClickHouse.DB.Model(&model.Task{})
	db = db.
		Joins("LEFT JOIN entity on entity.id = task.entity_id").
		Select("task.project_id as ProjectId,task.id as TaskId,entity.name as EntityName," +
			"entity.code as EntityCode,entity.initial_episode_code as EntityInitialEpisodeCode,task.step_id as TaskStepId," +
			"task.step_category_id as TaskStepCategoryId,task.project_id as TaskProjectId,task.tenant_id as TaskTenantId," +
			"task.department_id as TaskDepartmentId,task.assignee as TaskAssignee,task.executor as TaskExecutor,task.name as TaskName," +
			"task.plan_end_time as TaskPlanEndTime,task.end_time as TaskEndTime,task.tenant_id as TenantId,task.executor as TaskExecutor," +
			"task.assignee as TaskAssignee,JSONExtractString(task.json, 'task_status')  as TaskStatusId,entity.id as EntityId")

	// 逾期交付
	if param.StatisticType == "TotalOverdueDeliver" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("TotalOverdueDeliver", param, statusCodeMap))
	}
	// 仍逾期
	if param.StatisticType == "StillOverdue" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("StillOverdue", param, statusCodeMap))
	}
	// 总逾期
	if param.StatisticType == "TotalOverdue" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("TotalOverdue", param, statusCodeMap))
	}
	// 本周已完成
	if param.StatisticType == "final" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("final", param, statusCodeMap))
	}
	// 本周已交付
	if param.StatisticType == "paid" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("paid", param, statusCodeMap))
	}
	// 本周未完成
	if param.StatisticType == "undone" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("undone", param, statusCodeMap))
	}
	// 本周已逾期
	if param.StatisticType == "PastDue" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("PastDue", param, statusCodeMap))
	}
	// 本周应交付 [本周应交付 = 已完成+已交付+未完成+已逾期]
	if param.StatisticType == "ShouldBeDelivered" {
		ShouldBeDeliveredIds, ShouldBeDeliveredErr := t.GetShouldBeDeliveredData(param, statusCodeMap)
		if len(ShouldBeDeliveredIds) <= 0 {
			res = []TaskCompletedDataStruct{}
			return
		}
		err = ShouldBeDeliveredErr
		db.Where("task.id IN (?)", ShouldBeDeliveredIds)
	}
	// 超额-已完成
	if param.StatisticType == "ExcessFinal" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("ExcessFinal", param, statusCodeMap))
	}
	// 超额-已交付
	if param.StatisticType == "ExcessPaid" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("ExcessPaid", param, statusCodeMap))
	}
	// 超额-总超额
	if param.StatisticType == "Excess" {
		db.Where(TaskWeekMeetingService.TaskCompletionWhere("Excess", param, statusCodeMap))
	}
	err = db.
		Offset((param.Page[0] - 1) * param.Page[1]).Limit(param.Page[1]).
		Find(&res).
		Error
	return
}

// TaskCompletedReturnData
func (t *Task) TaskCompletedReturnData(taskCompletedData []TaskCompletedDataStruct, stepMap map[int64]*model.Step,
	projectMap map[uint64]*model.Project, tenantMap map[uint64]*model.Tenant, departmentMap map[uint64]*model.Department,
	userMap map[uint64]*model.User, statusMap map[int64]*model.Status) (res []types.WeekCompletionItem) {

	for _, v := range taskCompletedData {
		var WeekCompletionItem = types.WeekCompletionItem{
			Entity: types.EntityItem{
				Id:                 v.EntityId,
				Name:               v.EntityName,
				Code:               v.EntityCode,
				InitialEpisodeCode: v.EntityInitialEpisodeCode,
			},
			Task: types.TaskItem{
				Id:             v.TaskId,
				StepId:         v.TaskStepId,
				StepCategoryId: v.TaskStepCategoryId,
				ProjectId:      v.TaskProjectId,
				TenantId:       v.TaskTenantId,
				DepartmentId:   v.TaskDepartmentId,
				Assignee:       v.TaskAssignee,
				Executor:       v.TaskExecutor,
				Name:           v.TaskName,
				PlanEndTime:    v.TaskPlanEndTime,
				EndTime:        v.TaskEndTime,
			},
			Project: types.ProjectItem{
				Id:   v.TaskProjectId,
				Name: "",
				Code: "",
				Type: "",
			},
			Step: types.StepItem{
				Id:   v.TaskStepId,
				Name: "",
				Code: "",
			},
			Tenant: types.TenantItem{
				Id:       v.TenantId,
				Name:     "",
				Status:   "",
				Created:  0,
				Role:     "",
				CreateBy: "",
			},
			Department: types.DepartmentItem{
				Id:              v.TaskDepartmentId,
				Name:            "",
				TenantId:        0,
				ParentId:        0,
				Created:         0,
				StepCategoryIds: "",
			},
			Executor: types.ExecutorItem{
				Id:     v.TaskExecutor,
				Name:   "",
				Email:  "",
				Phone:  "",
				Avatar: "",
			},
			Assignee: types.AssigneeItem{
				Id:     v.TaskAssignee,
				Name:   "",
				Email:  "",
				Phone:  "",
				Avatar: "",
			},
			TaskStatus: types.TaskStatusItem{
				Id:   v.TaskStatusId,
				Name: "",
				Code: "",
				Icon: "",
			},
		}
		if _, ok := stepMap[v.TaskStepId]; ok {
			WeekCompletionItem.Step.Name = stepMap[v.TaskStepId].Name
			WeekCompletionItem.Step.Code = stepMap[v.TaskStepId].Code
		}
		if _, ok := projectMap[v.TaskProjectId]; ok {
			WeekCompletionItem.Project.Name = projectMap[v.TaskProjectId].Name
			WeekCompletionItem.Project.Code = projectMap[v.TaskProjectId].Code
			WeekCompletionItem.Project.Type = projectMap[v.TaskProjectId].Type
		}
		if _, ok := tenantMap[v.TaskTenantId]; ok {
			WeekCompletionItem.Tenant.Name = tenantMap[v.TaskTenantId].Name
			WeekCompletionItem.Tenant.Status = tenantMap[v.TaskTenantId].Status
			WeekCompletionItem.Tenant.Created = tenantMap[v.TaskTenantId].Created
			WeekCompletionItem.Tenant.Role = tenantMap[v.TaskTenantId].Role
			WeekCompletionItem.Tenant.CreateBy = tenantMap[v.TaskTenantId].CreateBy
		}
		if _, ok := departmentMap[v.TaskDepartmentId]; ok {
			WeekCompletionItem.Department.Name = departmentMap[v.TaskDepartmentId].Name
			WeekCompletionItem.Department.TenantId = departmentMap[v.TaskDepartmentId].TenantId
			WeekCompletionItem.Department.ParentId = departmentMap[v.TaskDepartmentId].ParentId
			WeekCompletionItem.Department.Created = departmentMap[v.TaskDepartmentId].Created
			WeekCompletionItem.Department.StepCategoryIds = departmentMap[v.TaskDepartmentId].StepCategoryIds
		}
		if _, ok := userMap[v.TaskExecutor]; ok {
			WeekCompletionItem.Executor.Name = userMap[v.TaskExecutor].Name
			WeekCompletionItem.Executor.Email = userMap[v.TaskExecutor].Email
			WeekCompletionItem.Executor.Phone = userMap[v.TaskExecutor].Phone
			WeekCompletionItem.Executor.Avatar = userMap[v.TaskExecutor].Avatar
		}
		if _, ok := userMap[v.TaskAssignee]; ok {
			WeekCompletionItem.Assignee.Name = userMap[v.TaskAssignee].Name
			WeekCompletionItem.Assignee.Email = userMap[v.TaskAssignee].Email
			WeekCompletionItem.Assignee.Phone = userMap[v.TaskAssignee].Phone
			WeekCompletionItem.Assignee.Avatar = userMap[v.TaskAssignee].Avatar
		}
		if _, ok := statusMap[v.TaskStatusId]; ok {
			WeekCompletionItem.TaskStatus.Name = statusMap[v.TaskStatusId].Name
			WeekCompletionItem.TaskStatus.Code = statusMap[v.TaskStatusId].Code
			WeekCompletionItem.TaskStatus.Icon = statusMap[v.TaskStatusId].Icon
		}
		res = append(res, WeekCompletionItem)
	}
	return
}

// TaskCompletionList 任务完成情况列表
func (t *Task) TaskCompletionList(req types.TaskCompletionListReq, tenantId int64) (res types.ResWeekCompletionItem, err error) {

	// 状态
	var statusService Status
	statusList, statusErr := statusService.GetStatus()
	if statusErr != nil {
		err = statusErr
		return
	}

	notCode := map[string]bool{
		"ready_to_publish": true,
		"final":            true,
		"closed":           true,
	}
	var statusMap = make(map[int64]*model.Status)
	var statusCodeMap = make(map[string]*model.Status)
	var notStatusIds = make([]int64, 0) // 排除待发布,已完成,已取消
	for i, status := range statusList {
		statusMap[status.Id] = &statusList[i]
		statusCodeMap[status.Code] = &statusList[i]
		if _, ok := notCode[status.Code]; !ok {
			notStatusIds = append(notStatusIds, status.Id)
		}
	}
	var stepCategoryIds []uint64 // 工序分类信息
	var stepCategoryService StepCategory
	stepCategoryList, stepCategoryErr := stepCategoryService.GetStepCategoryList(ClickHouse.DB.Where("code in (?)", req.StepCategoryCode))
	if stepCategoryErr != nil {
		err = stepCategoryErr
		return
	}
	for _, v := range stepCategoryList {
		stepCategoryIds = append(stepCategoryIds, v.Id)
	}

	var TaskCompletedParam types.GetTaskCompletionReq
	TaskCompletedParam.ProjectIdList = req.ProjectIdList
	TaskCompletedParam.StartTime = req.StartTime
	TaskCompletedParam.EndTime = req.EndTime
	TaskCompletedParam.Page = req.Page
	TaskCompletedParam.TenantId = tenantId
	TaskCompletedParam.StatisticType = req.StatisticType
	TaskCompletedParam.StepCategoryIds = stepCategoryIds
	TaskCompletedParam.NotStatusIds = notStatusIds
	TaskCompletedParam.StepCategoryCode = req.StepCategoryCode
	taskCompletedData, taskCompletedErr := t.GetTaskCompletedData(TaskCompletedParam, statusCodeMap)
	if taskCompletedErr != nil {
		err = taskCompletedErr
		return
	}

	userIdList := make([]uint64, 0)
	userIdMap := make(map[uint64]bool, 0)
	stepIdList := make([]int64, 0)
	stepIdMap := make(map[int64]bool, 0)
	tenantIdList := make([]uint64, 0)
	tenantIdMap := make(map[uint64]bool, 0)
	projectIdList := make([]uint64, 0)
	projectIdMap := make(map[uint64]bool, 0)
	departmentIdList := make([]uint64, 0)
	departmentIdMap := make(map[uint64]bool, 0)
	for _, v := range taskCompletedData {

		if v.TaskDepartmentId > 0 {
			if _, ok := departmentIdMap[v.TaskDepartmentId]; !ok {
				departmentIdList = append(departmentIdList, v.TaskDepartmentId)
			}
		}
		if v.TaskTenantId > 0 {
			if _, ok := tenantIdMap[v.TaskTenantId]; !ok {
				tenantIdList = append(tenantIdList, v.TaskTenantId)
			}
		}
		if v.TaskProjectId > 0 {
			if _, ok := projectIdMap[v.TaskProjectId]; !ok {
				projectIdList = append(projectIdList, v.TaskProjectId)
			}
		}
		if v.TaskStepId > 0 {
			if _, ok := stepIdMap[v.TaskStepId]; !ok {
				stepIdList = append(stepIdList, v.TaskStepId)
			}
		}
		if v.TaskExecutor > 0 {
			if _, ok := userIdMap[v.TaskExecutor]; !ok {
				userIdList = append(userIdList, v.TaskExecutor)
			}
			userIdMap[v.TaskExecutor] = true
		}
		if v.TaskAssignee > 0 {
			if _, ok := userIdMap[v.TaskAssignee]; !ok {
				userIdList = append(userIdList, v.TaskAssignee)
			}
			userIdMap[v.TaskAssignee] = true
		}
		stepIdMap[v.TaskStepId] = true
		tenantIdMap[v.TaskTenantId] = true
		projectIdMap[v.TaskProjectId] = true
		departmentIdMap[v.TaskDepartmentId] = true
	}

	// 项目
	var projectMap = make(map[uint64]*model.Project)
	if len(projectIdList) > 0 {
		var projectService Project
		projectList, projectErr := projectService.GetList(ClickHouse.DB.Where("id in ?", projectIdList))
		if projectErr != nil {
			err = projectErr
			return
		}
		for i, v := range projectList {
			projectMap[v.Id] = &projectList[i]
		}
	}
	// 工序
	var stepMap = make(map[int64]*model.Step)
	if len(stepIdList) > 0 {
		var stepService Step
		stepList, stepErr := stepService.GetStepList(ClickHouse.DB.Where("id in ?", stepIdList))
		if stepErr != nil {
			err = stepErr
			return
		}
		for i, step := range stepList {
			stepMap[step.Id] = &stepList[i]
		}
	}
	// 租户
	var tenantMap = make(map[uint64]*model.Tenant)
	if len(tenantIdList) > 0 {
		var tenantService Tenant
		tenantList, tenantErr := tenantService.GetList(ClickHouse.DB.Where("id in ?", tenantIdList))
		if tenantErr != nil {
			err = tenantErr
			return
		}
		for i, tenant := range tenantList {
			tenantMap[uint64(tenant.Id)] = &tenantList[i]
		}
	}
	// 部门
	var departmentMap = make(map[uint64]*model.Department)
	if len(departmentIdList) > 0 {
		var departmentService Department
		departmentList, departmentErr := departmentService.getDepartmentList(tenantId, ClickHouse.DB.Where("id in ?", departmentIdList))
		if departmentErr != nil {
			err = departmentErr
			return
		}
		for i, department := range departmentList {
			departmentMap[department.Id] = &departmentList[i]
		}
	}
	// 用户
	var userMap = make(map[uint64]*model.User)
	if len(userIdList) > 0 {
		var userService User
		userList, userErr := userService.GetUserList(ClickHouse.DB.Where("id in ?", userIdList))
		if userErr != nil {
			err = userErr
			return
		}
		for i, user := range userList {
			userMap[user.Id] = &userList[i]
		}
	}
	res = t.TaskCompletedReturnData(taskCompletedData, stepMap, projectMap, tenantMap, departmentMap, userMap, statusMap)
	return
}

func (t *Task) getUserCountDetailQuery(req types.UserCountDetailReq) (res *gorm.DB) {
	res = ClickHouse.DB.Model(model.Task{}).
		Joins("LEFT JOIN task_member on task_member.task_id = task.id LEFT JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = task.id "+
			"LEFT JOIN (select * from task_relation_tag where link_module_code in ('episode')) r_episode on r_episode.task_id = task.id "+
			"LEFT JOIN plan on plan.task_id = task.id").
		Where("task_member.partner = ?", req.UserId)
	if len(req.ProjectId) > 0 {
		res = res.Where("task.project_id in ?", req.ProjectId)
	}
	//if req.StartTime > 0 && req.EndTime > 0 {
	//	res = res.Where("task.plan_end_time between ? and ?", req.StartTime, req.EndTime)
	//}
	if len(req.StepCategoryIdList) > 0 {
		res = res.Where("task.step_category_id in ? or r_step_category.link_id in ?", req.StepCategoryIdList, req.StepCategoryIdList)
	}

	return
}

type PlanEpisodeStepCategory struct {
	EpisodeId      int64
	StepCategoryId int64
	MaxEndTime     int64
}

// UserCountDetail 用户任务统计详情
func (t *Task) UserCountDetail(ctx *gin.Context, req types.UserCountDetailReq) (res types.UserCountDetailRes, err error) {
	var userService User
	unionId, err := userService.GetUnionIdByUserId(req.UserId)
	if err != nil {
		return
	}
	userInfo, err := userService.GetOtherUserInfo(ctx, unionId)
	if err != nil {
		return res, err
	}
	res.UserInfo = userInfo.Data

	var reqStepCategory []int64
	for _, id := range req.StepCategoryIdList {
		if id > 0 {
			reqStepCategory = append(reqStepCategory, id)
		}
	}
	req.StepCategoryIdList = reqStepCategory

	var extraWhere *gorm.DB = ClickHouse.DB
	if len(reqStepCategory) > 0 {
		// 排期
		pq := ClickHouse.DB.Model(model.Plan{}).
			Joins("LEFT JOIN (select * from task_relation_tag where link_module_code in ('step_category','show_step_category')) r_step_category on r_step_category.task_id = plan.task_id " +
				"LEFT JOIN (select * from task_relation_tag where link_module_code in ('episode')) r_episode on r_episode.task_id = plan.task_id").
			Where("plan.scope='single' and plan.type = 'step_category'").
			Group("r_episode.link_id,r_step_category.link_id")

		if len(req.ProjectId) > 0 {
			pq = pq.Where("plan.project_id in ?", req.ProjectId)
		}
		if req.StartTime > 0 && req.EndTime > 0 {
			pq = pq.Where("plan.end_time between ? and ?", req.StartTime, req.EndTime)
		}
		if len(req.StepCategoryIdList) > 0 {
			pq = pq.Where("r_step_category.link_id in ? ", req.StepCategoryIdList)
		}

		var pqRes []PlanEpisodeStepCategory
		err = pq.Select("r_episode.link_id as episode_id,r_step_category.link_id as step_category_id").Find(&pqRes).Error
		if err != nil || len(pqRes) == 0 {
			return
		}
		for _, re := range pqRes {
			extraWhere = extraWhere.Or("(task.episode_id = ? or r_episode.link_id = ?) and (task.step_category_id = ? or r_step_category.link_id = ?)", re.EpisodeId, re.EpisodeId, re.StepCategoryId, re.StepCategoryId)
		}
	} else {
		if req.StartTime > 0 && req.EndTime > 0 {
			extraWhere = extraWhere.Where("plan.end_time between ? and ?", req.StartTime, req.EndTime)
		}
	}

	err = t.getUserCountDetailQuery(req).Where(extraWhere).Pluck("count(distinct task.id) as taskTotal", &res.TaskTotal).Error
	if err != nil {
		return
	}
	if res.TaskTotal > 0 {
		err = t.getUserCountDetailQuery(req).Where(extraWhere).
			Where("simpleJSONExtractInt(task.json, 'task_status') = 7 or task_member.status = 7").
			Pluck("count(distinct task.id)", &res.TaskDone).Error
		res.TaskDonePercent = float64(res.TaskDone) / float64(res.TaskTotal)
	}

	return
}
